Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select setseed(.42);--stabilise `random()` between re-runs
--generate samples
create table tbl as 
select (random()*1e3)::int as id
     , initcap((array_sample(--https://www.randomlists.com/things?show_images=false&dup=false&qty=200
          array['USB drive','deodorant','sailboat','bowl','pencil','bottle cap','scotch tape','water bottle','bananas','cell phone','shampoo','lotion','shawl','teddies','sponge','floor','lamp shade','greeting card','cookie jar','flag','sun glasses','headphones','controller','buckle','flowers','clock','rubber duck','ring','mirror','fake flowers','bow','spring','magnet','knife','soda can','lip gloss','cup','white out','desk','food','chapter book','candy wrapper','nail file','screw','drill press','sketch pad','computer','bag','thermometer','clay pot','rubber band','chair','canvas','hair tie','pen','chalk','piano','ice cube tray','seat belt','blouse','zipper','cinder block','outlet','remote','plastic fork','stockings','shovel','puddle','key chain','sofa','monitor','tomato','helmet','beef','face wash','stop sign','clothes','video games','model car','bookmark','house','button','packing peanuts','thermostat','bed','fork','mouse pad','sticky note','pool stick','leg warmers','street lights','apple','charger','air freshener','keys','window','sharpie','photo album','slipper','clamp','thread','bracelet','newspaper','twezzers','needle','soap','wallet','box','pillow','rusty nail','door','spoon','glow stick','shirt','eye liner','book','sidewalk','toothbrush','phone','glasses','ipod','keyboard','nail clippers','checkbook','bread','cat','coasters','pants','speakers','carrots','twister','paper','credit card','towel','CD','money','socks','tv','tire swing','toilet','plate','bottle','playing card','picture frame','grid paper','camera','mp3 player','toe ring','car','candle','tooth picks','radio','hair brush','truck','sand paper','lace','conditioner','vase','purse','paint brush','tissue box','eraser','shoes','milk','toothpaste','cork','chocolate','fridge','lamp','doll','soy sauce packet','wagon','watch','mop','balloon','shoe lace','perfume','drawer','sandal','boom box','table','hanger','rug','blanket','couch','television','washing machine','brocolli','tree','glass'
               ],1))[1]) as product
     , (random()*1e3)::numeric(6,1)::numeric as cost
from generate_series(1,3e5);
--inspect samples
select * from tbl tablesample bernoulli(.42)repeatable(.42)limit 8;

create index idx1 on tbl (id);
create index idx2 on tbl (product);
create index idx3 on tbl (cost);

setseed

SELECT 1

SELECT 300000

id
product
cost

802
Grid Paper
504.1

811
Shovel
685.7

831
Mouse Pad
800.3

888
Cup
903.0

443
Fridge
578.0

135
Brocolli
458.2

809
Bread
210.0

16
Vase
56.4

SELECT 8

CREATE INDEX

CREATE INDEX

CREATE INDEX

vacuum analyze tbl;

VACUUM

--this would pop the values from each of the 3 indexes
prepare subqueries_three_on_3_indexes(int) as
select (select array_agg(id) from (select distinct id from tbl limit $1)_)
      ,(select array_agg(product) from (select distinct product from tbl limit $1)_)
      ,(select array_agg(cost) from (select distinct cost from tbl limit $1)_);

explain analyze verbose execute subqueries_three_on_3_indexes(3);
execute subqueries_three_on_3_indexes(3);

explain analyze verbose execute subqueries_three_on_3_indexes(30);
execute subqueries_three_on_3_indexes(30);

explain analyze verbose execute subqueries_three_on_3_indexes(120);
execute subqueries_three_on_3_indexes(120);

PREPARE

QUERY PLAN

Result  (cost=126.35..126.36 rows=1 width=96) (actual time=0.829..0.831 rows=1 loops=1)

  Output: $0, $1, $2

  InitPlan 1 (returns $0)

    ->  Aggregate  (cost=19.84..19.85 rows=1 width=32) (actual time=0.125..0.125 rows=1 loops=1)

          Output: array_agg(tbl.id)

          ->  Limit  (cost=0.42..19.83 rows=3 width=4) (actual time=0.043..0.110 rows=3 loops=1)

                Output: tbl.id

                ->  Unique  (cost=0.42..6474.42 rows=1001 width=4) (actual time=0.042..0.108 rows=3 loops=1)

                      Output: tbl.id

                      ->  Index Only Scan using idx1 on public.tbl  (cost=0.42..5724.42 rows=300000 width=4) (actual time=0.041..0.081 rows=430 loops=1)

                            Output: tbl.id

                            Heap Fetches: 0

  InitPlan 2 (returns $1)

    ->  Aggregate  (cost=103.41..103.42 rows=1 width=32) (actual time=0.640..0.640 rows=1 loops=1)

          Output: array_agg(tbl_1.product)

          ->  Limit  (cost=0.42..103.40 rows=3 width=8) (actual time=0.035..0.635 rows=3 loops=1)

                Output: tbl_1.product

                ->  Unique  (cost=0.42..6522.42 rows=190 width=8) (actual time=0.035..0.634 rows=3 loops=1)

                      Output: tbl_1.product

                      ->  Index Only Scan using idx2 on public.tbl tbl_1  (cost=0.42..5772.42 rows=300000 width=8) (actual time=0.034..0.365 rows=3155 loops=1)

                            Output: tbl_1.product

                            Heap Fetches: 0

  InitPlan 3 (returns $2)

    ->  Aggregate  (cost=3.07..3.08 rows=1 width=32) (actual time=0.056..0.056 rows=1 loops=1)

          Output: array_agg(tbl_2.cost)

          ->  Limit  (cost=0.42..3.06 rows=3 width=6) (actual time=0.038..0.053 rows=3 loops=1)

                Output: tbl_2.cost

                ->  Unique  (cost=0.42..8750.42 rows=9958 width=6) (actual time=0.038..0.052 rows=3 loops=1)

                      Output: tbl_2.cost

                      ->  Index Only Scan using idx3 on public.tbl tbl_2  (cost=0.42..8000.42 rows=300000 width=6) (actual time=0.037..0.043 rows=54 loops=1)

                            Output: tbl_2.cost

                            Heap Fetches: 0

Planning Time: 1.046 ms

Execution Time: 0.900 ms

EXPLAIN

array_agg
array_agg
array_agg

{0,1,2}
{"Air Freshener",Apple,Bag}
{0.0,0.1,0.2}

SELECT 1

QUERY PLAN

Result  (cost=1251.71..1251.72 rows=1 width=96) (actual time=10.136..10.138 rows=1 loops=1)

  Output: $0, $1, $2

  InitPlan 1 (returns $0)

    ->  Aggregate  (cost=194.53..194.54 rows=1 width=32) (actual time=1.455..1.456 rows=1 loops=1)

          Output: array_agg(tbl.id)

          ->  Limit  (cost=0.42..194.45 rows=30 width=4) (actual time=0.010..1.447 rows=30 loops=1)

                Output: tbl.id

                ->  Unique  (cost=0.42..6474.42 rows=1001 width=4) (actual time=0.009..1.443 rows=30 loops=1)

                      Output: tbl.id

                      ->  Index Only Scan using idx1 on public.tbl  (cost=0.42..5724.42 rows=300000 width=4) (actual time=0.009..0.928 rows=8509 loops=1)

                            Output: tbl.id

                            Heap Fetches: 0

  InitPlan 2 (returns $1)

    ->  Aggregate  (cost=1030.29..1030.30 rows=1 width=32) (actual time=8.425..8.425 rows=1 loops=1)

          Output: array_agg(tbl_1.product)

          ->  Limit  (cost=0.42..1030.21 rows=30 width=8) (actual time=0.008..8.408 rows=30 loops=1)

                Output: tbl_1.product

                ->  Unique  (cost=0.42..6522.42 rows=190 width=8) (actual time=0.007..8.402 rows=30 loops=1)

                      Output: tbl_1.product

                      ->  Index Only Scan using idx2 on public.tbl tbl_1  (cost=0.42..5772.42 rows=300000 width=8) (actual time=0.007..4.744 rows=45855 loops=1)

                            Output: tbl_1.product

                            Heap Fetches: 0

  InitPlan 3 (returns $2)

    ->  Aggregate  (cost=26.86..26.87 rows=1 width=32) (actual time=0.252..0.252 rows=1 loops=1)

          Output: array_agg(tbl_2.cost)

          ->  Limit  (cost=0.42..26.78 rows=30 width=6) (actual time=0.015..0.244 rows=30 loops=1)

                Output: tbl_2.cost

                ->  Unique  (cost=0.42..8750.42 rows=9958 width=6) (actual time=0.014..0.241 rows=30 loops=1)

                      Output: tbl_2.cost

                      ->  Index Only Scan using idx3 on public.tbl tbl_2  (cost=0.42..8000.42 rows=300000 width=6) (actual time=0.014..0.126 rows=814 loops=1)

                            Output: tbl_2.cost

                            Heap Fetches: 0

Planning Time: 0.328 ms

Execution Time: 10.170 ms

EXPLAIN

array_agg
array_agg
array_agg

{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29}
{"Air Freshener",Apple,Bag,Balloon,Bananas,Bed,Beef,Blanket,Blouse,Book,Bookmark,"Boom Box",Bottle,"Bottle Cap",Bow,Bowl,Box,Bracelet,Bread,Brocolli,Buckle,Button,Camera,Candle,"Candy Wrapper",Canvas,Car,Carrots,Cat,Cd}
{0.0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1.0,1.1,1.2,1.3,1.4,1.5,1.6,1.7,1.8,1.9,2.0,2.1,2.2,2.3,2.4,2.5,2.6,2.7,2.8,2.9}

SELECT 1

QUERY PLAN

Result  (cost=4649.65..4649.66 rows=1 width=96) (actual time=75.472..77.786 rows=1 loops=1)

  Output: $0, $2, $3

  InitPlan 1 (returns $0)

    ->  Aggregate  (cost=776.83..776.84 rows=1 width=32) (actual time=5.834..5.836 rows=1 loops=1)

          Output: array_agg(tbl.id)

          ->  Limit  (cost=0.42..776.53 rows=120 width=4) (actual time=0.009..5.811 rows=120 loops=1)

                Output: tbl.id

                ->  Unique  (cost=0.42..6474.42 rows=1001 width=4) (actual time=0.009..5.798 rows=120 loops=1)

                      Output: tbl.id

                      ->  Index Only Scan using idx1 on public.tbl  (cost=0.42..5724.42 rows=300000 width=4) (actual time=0.008..3.725 rows=35421 loops=1)

                            Output: tbl.id

                            Heap Fetches: 0

  InitPlan 2 (returns $2)

    ->  Aggregate  (cost=3766.63..3766.64 rows=1 width=32) (actual time=68.604..70.915 rows=1 loops=1)

          Output: array_agg(tbl_1.product)

          ->  Limit  (cost=1000.45..3766.33 rows=120 width=8) (actual time=68.310..70.883 rows=120 loops=1)

                Output: tbl_1.product

                ->  Unique  (cost=1000.45..5379.76 rows=190 width=8) (actual time=68.309..70.871 rows=120 loops=1)

                      Output: tbl_1.product

                      ->  Gather Merge  (cost=1000.45..5378.81 rows=380 width=8) (actual time=68.308..70.850 rows=128 loops=1)

                            Output: tbl_1.product

                            Workers Planned: 2

                            Workers Launched: 2

                            ->  Unique  (cost=0.42..4334.92 rows=190 width=8) (actual time=0.022..40.109 rows=67 loops=3)

                                  Output: tbl_1.product

                                  Worker 0:  actual time=0.025..59.664 rows=102 loops=1

                                  Worker 1:  actual time=0.031..60.455 rows=98 loops=1

                                  ->  Parallel Index Only Scan using idx2 on public.tbl tbl_1  (cost=0.42..4022.42 rows=125000 width=8) (actual time=0.021..27.821 rows=100000 loops=3)

                                        Output: tbl_1.product

                                        Heap Fetches: 0

                                        Worker 0:  actual time=0.024..38.862 rows=152711 loops=1

                                        Worker 1:  actual time=0.029..44.482 rows=146133 loops=1

  InitPlan 3 (returns $3)

    ->  Aggregate  (cost=106.17..106.18 rows=1 width=32) (actual time=1.026..1.027 rows=1 loops=1)

          Output: array_agg(tbl_2.cost)

          ->  Limit  (cost=0.42..105.87 rows=120 width=6) (actual time=0.022..1.005 rows=120 loops=1)

                Output: tbl_2.cost

                ->  Unique  (cost=0.42..8750.42 rows=9958 width=6) (actual time=0.021..0.994 rows=120 loops=1)

                      Output: tbl_2.cost

                      ->  Index Only Scan using idx3 on public.tbl tbl_2  (cost=0.42..8000.42 rows=300000 width=6) (actual time=0.021..0.499 rows=3446 loops=1)

                            Output: tbl_2.cost

                            Heap Fetches: 0

Planning Time: 0.180 ms

Execution Time: 77.841 ms

EXPLAIN

array_agg
array_agg
array_agg

{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119}
{"Air Freshener",Apple,Bag,Balloon,Bananas,Bed,Beef,Blanket,Blouse,Book,Bookmark,"Boom Box",Bottle,"Bottle Cap",Bow,Bowl,Box,Bracelet,Bread,Brocolli,Buckle,Button,Camera,Candle,"Candy Wrapper",Canvas,Car,Carrots,Cat,Cd,"Cell Phone",Chair,Chalk,"Chapter Book",Charger,Checkbook,Chocolate,"Cinder Block",Clamp,"Clay Pot",Clock,Clothes,Coasters,Computer,Conditioner,Controller,"Cookie Jar",Cork,Couch,"Credit Card",Cup,Deodorant,Desk,Doll,Door,Drawer,"Drill Press",Eraser,"Eye Liner","Face Wash","Fake Flowers",Flag,Floor,Flowers,Food,Fork,Fridge,Glass,Glasses,"Glow Stick","Greeting Card","Grid Paper","Hair Brush","Hair Tie",Hanger,Headphones,Helmet,House,"Ice Cube Tray",Ipod,"Key Chain",Keyboard,Keys,Knife,Lace,Lamp,"Lamp Shade","Leg Warmers","Lip Gloss",Lotion,Magnet,Milk,Mirror,"Model Car",Money,Monitor,Mop,"Mouse Pad","Mp3 Player","Nail Clippers","Nail File",Needle,Newspaper,Outlet,"Packing Peanuts","Paint Brush",Pants,Paper,Pen,Pencil,Perfume,Phone,"Photo Album",Piano,"Picture Frame",Pillow,"Plastic Fork",Plate,"Playing Card","Pool Stick"}
{0.0,0.1,0.2,0.3,0.4,0.5,0.6,0.7,0.8,0.9,1.0,1.1,1.2,1.3,1.4,1.5,1.6,1.7,1.8,1.9,2.0,2.1,2.2,2.3,2.4,2.5,2.6,2.7,2.8,2.9,3.0,3.1,3.2,3.3,3.4,3.5,3.6,3.7,3.8,3.9,4.0,4.1,4.2,4.3,4.4,4.5,4.6,4.7,4.8,4.9,5.0,5.1,5.2,5.3,5.4,5.5,5.6,5.7,5.8,5.9,6.0,6.1,6.2,6.3,6.4,6.5,6.6,6.7,6.8,6.9,7.0,7.1,7.2,7.3,7.4,7.5,7.6,7.7,7.8,7.9,8.0,8.1,8.2,8.3,8.4,8.5,8.6,8.7,8.8,8.9,9.0,9.1,9.2,9.3,9.4,9.5,9.6,9.7,9.8,9.9,10.0,10.1,10.2,10.3,10.4,10.5,10.6,10.7,10.8,10.9,11.0,11.1,11.2,11.3,11.4,11.5,11.6,11.7,11.8,11.9}

SELECT 1

--get one row and iteratively collect more of what you don't already have
--doesn't care about the index
prepare recursive_cte_3way_skip_scan(int) as 
with recursive cte as (
( select   1 as i
          , array[id] ids 
          , array[product] products
          , array[cost] costs
  from tbl limit 1) --get 1 random, most convenient value
  union all
  select cte.i+1 as i
       ,  (select array[id] ids 
           from tbl 
           where array_position(cte.ids,tbl.id) is null 
           limit 1) ||cte.ids
       ,  (select array[product] products 
           from tbl 
           where array_position(cte.products,tbl.product) is null
           limit 1) ||cte.products
       ,  (select array[cost] costs 
           from tbl 
           where array_position(cte.costs,tbl.cost) is null
           limit 1) ||cte.costs
  from cte where $1>i
) 
select ids, products, costs
from cte order by i desc limit 1;

explain analyze verbose execute recursive_cte_3way_skip_scan(3);
execute recursive_cte_3way_skip_scan(3);

explain analyze verbose execute recursive_cte_3way_skip_scan(30);
execute recursive_cte_3way_skip_scan(30);

explain analyze verbose execute recursive_cte_3way_skip_scan(120);
execute recursive_cte_3way_skip_scan(120);

PREPARE

QUERY PLAN

Limit  (cost=343.85..343.85 rows=1 width=100) (actual time=0.077..0.079 rows=1 loops=1)

  Output: cte.ids, cte.products, cte.costs, cte.i

  CTE cte

    ->  Recursive Union  (cost=0.00..343.08 rows=31 width=100) (actual time=0.012..0.061 rows=3 loops=1)

          ->  Limit  (cost=0.00..0.02 rows=1 width=100) (actual time=0.011..0.011 rows=1 loops=1)

                Output: 1, (ARRAY[tbl_3.id]), (ARRAY[tbl_3.product]), (ARRAY[tbl_3.cost])

                ->  Seq Scan on public.tbl tbl_3  (cost=0.00..4920.00 rows=300000 width=100) (actual time=0.011..0.011 rows=1 loops=1)

                      Output: 1, ARRAY[tbl_3.id], ARRAY[tbl_3.product], ARRAY[tbl_3.cost]

          ->  WorkTable Scan on cte cte_1  (cost=0.00..34.27 rows=3 width=100) (actual time=0.015..0.015 rows=1 loops=3)

                Output: (cte_1.i + 1), ((SubPlan 1) || cte_1.ids), ((SubPlan 2) || cte_1.products), ((SubPlan 3) || cte_1.costs)

                Filter: (3 > cte_1.i)

                Rows Removed by Filter: 0

                SubPlan 1

                  ->  Limit  (cost=0.00..3.78 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=2)

                        Output: (ARRAY[tbl.id])

                        ->  Seq Scan on public.tbl  (cost=0.00..5670.00 rows=1500 width=32) (actual time=0.005..0.005 rows=1 loops=2)

                              Output: ARRAY[tbl.id]

                              Filter: (array_position(cte_1.ids, tbl.id) IS NULL)

                              Rows Removed by Filter: 2

                SubPlan 2

                  ->  Limit  (cost=0.00..3.78 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=2)

                        Output: (ARRAY[tbl_1.product])

                        ->  Seq Scan on public.tbl tbl_1  (cost=0.00..5670.00 rows=1500 width=32) (actual time=0.004..0.004 rows=1 loops=2)

                              Output: ARRAY[tbl_1.product]

                              Filter: (array_position(cte_1.products, tbl_1.product) IS NULL)

                              Rows Removed by Filter: 2

                SubPlan 3

                  ->  Limit  (cost=0.00..3.78 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=2)

                        Output: (ARRAY[tbl_2.cost])

                        ->  Seq Scan on public.tbl tbl_2  (cost=0.00..5670.00 rows=1500 width=32) (actual time=0.004..0.004 rows=1 loops=2)

                              Output: ARRAY[tbl_2.cost]

                              Filter: (array_position(cte_1.costs, tbl_2.cost) IS NULL)

                              Rows Removed by Filter: 2

  ->  Sort  (cost=0.78..0.85 rows=31 width=100) (actual time=0.076..0.077 rows=1 loops=1)

        Output: cte.ids, cte.products, cte.costs, cte.i

        Sort Key: cte.i DESC

        Sort Method: top-N heapsort  Memory: 25kB

        ->  CTE Scan on cte  (cost=0.00..0.62 rows=31 width=100) (actual time=0.014..0.064 rows=3 loops=1)

              Output: cte.ids, cte.products, cte.costs, cte.i

Planning Time: 0.307 ms

Execution Time: 0.120 ms

EXPLAIN

ids
products
costs

{94,937,743}
{"Mouse Pad",Floor,Fork}
{213.9,64.3,362.5}

SELECT 1

QUERY PLAN

Limit  (cost=343.85..343.85 rows=1 width=100) (actual time=1.149..1.151 rows=1 loops=1)

  Output: cte.ids, cte.products, cte.costs, cte.i

  CTE cte

    ->  Recursive Union  (cost=0.00..343.08 rows=31 width=100) (actual time=0.009..1.110 rows=30 loops=1)

          ->  Limit  (cost=0.00..0.02 rows=1 width=100) (actual time=0.008..0.009 rows=1 loops=1)

                Output: 1, (ARRAY[tbl_3.id]), (ARRAY[tbl_3.product]), (ARRAY[tbl_3.cost])

                ->  Seq Scan on public.tbl tbl_3  (cost=0.00..4920.00 rows=300000 width=100) (actual time=0.008..0.008 rows=1 loops=1)

                      Output: 1, ARRAY[tbl_3.id], ARRAY[tbl_3.product], ARRAY[tbl_3.cost]

          ->  WorkTable Scan on cte cte_1  (cost=0.00..34.27 rows=3 width=100) (actual time=0.035..0.036 rows=1 loops=30)

                Output: (cte_1.i + 1), ((SubPlan 1) || cte_1.ids), ((SubPlan 2) || cte_1.products), ((SubPlan 3) || cte_1.costs)

                Filter: (30 > cte_1.i)

                Rows Removed by Filter: 0

                SubPlan 1

                  ->  Limit  (cost=0.00..3.78 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=29)

                        Output: (ARRAY[tbl.id])

                        ->  Seq Scan on public.tbl  (cost=0.00..5670.00 rows=1500 width=32) (actual time=0.007..0.007 rows=1 loops=29)

                              Output: ARRAY[tbl.id]

                              Filter: (array_position(cte_1.ids, tbl.id) IS NULL)

                              Rows Removed by Filter: 15

                SubPlan 2

                  ->  Limit  (cost=0.00..3.78 rows=1 width=32) (actual time=0.012..0.012 rows=1 loops=29)

                        Output: (ARRAY[tbl_1.product])

                        ->  Seq Scan on public.tbl tbl_1  (cost=0.00..5670.00 rows=1500 width=32) (actual time=0.011..0.011 rows=1 loops=29)

                              Output: ARRAY[tbl_1.product]

                              Filter: (array_position(cte_1.products, tbl_1.product) IS NULL)

                              Rows Removed by Filter: 16

                SubPlan 3

                  ->  Limit  (cost=0.00..3.78 rows=1 width=32) (actual time=0.015..0.015 rows=1 loops=29)

                        Output: (ARRAY[tbl_2.cost])

                        ->  Seq Scan on public.tbl tbl_2  (cost=0.00..5670.00 rows=1500 width=32) (actual time=0.014..0.014 rows=1 loops=29)

                              Output: ARRAY[tbl_2.cost]

                              Filter: (array_position(cte_1.costs, tbl_2.cost) IS NULL)

                              Rows Removed by Filter: 15

  ->  Sort  (cost=0.78..0.85 rows=31 width=100) (actual time=1.149..1.149 rows=1 loops=1)

        Output: cte.ids, cte.products, cte.costs, cte.i

        Sort Key: cte.i DESC

        Sort Method: top-N heapsort  Memory: 26kB

        ->  CTE Scan on cte  (cost=0.00..0.62 rows=31 width=100) (actual time=0.010..1.131 rows=30 loops=1)

              Output: cte.ids, cte.products, cte.costs, cte.i

Planning Time: 0.199 ms

Execution Time: 1.178 ms

EXPLAIN

ids
products
costs

{905,29,632,108,188,314,119,917,235,826,783,854,274,272,206,489,943,977,423,410,82,312,131,838,570,575,491,94,937,743}
{Flowers,"Toe Ring","Sun Glasses",Sandal,Twister,"Sketch Pad","Usb Drive",Socks,Chocolate,Mirror,Lace,"Tire Swing",Sidewalk,Newspaper,Apple,Charger,"Cell Phone",Towel,"Boom Box","Glow Stick","Lamp Shade","Face Wash",Coasters,Monitor,Thermometer,Speakers,Eraser,"Mouse Pad",Floor,Fork}
{765.6,503.7,167.3,355.4,945.5,196.4,111.8,20.7,647.4,18.8,181.1,939.7,121.5,393.6,637.3,638.3,355.1,881.8,465.3,317.1,810.7,875.9,194.1,827.5,969.5,754.0,389.2,213.9,64.3,362.5}

SELECT 1

QUERY PLAN

Limit  (cost=343.85..343.85 rows=1 width=100) (actual time=39.844..39.847 rows=1 loops=1)

  Output: cte.ids, cte.products, cte.costs, cte.i

  CTE cte

    ->  Recursive Union  (cost=0.00..343.08 rows=31 width=100) (actual time=0.010..39.645 rows=120 loops=1)

          ->  Limit  (cost=0.00..0.02 rows=1 width=100) (actual time=0.009..0.010 rows=1 loops=1)

                Output: 1, (ARRAY[tbl_3.id]), (ARRAY[tbl_3.product]), (ARRAY[tbl_3.cost])

                ->  Seq Scan on public.tbl tbl_3  (cost=0.00..4920.00 rows=300000 width=100) (actual time=0.009..0.009 rows=1 loops=1)

                      Output: 1, ARRAY[tbl_3.id], ARRAY[tbl_3.product], ARRAY[tbl_3.cost]

          ->  WorkTable Scan on cte cte_1  (cost=0.00..34.27 rows=3 width=100) (actual time=0.329..0.329 rows=1 loops=120)

                Output: (cte_1.i + 1), ((SubPlan 1) || cte_1.ids), ((SubPlan 2) || cte_1.products), ((SubPlan 3) || cte_1.costs)

                Filter: (120 > cte_1.i)

                Rows Removed by Filter: 0

                SubPlan 1

                  ->  Limit  (cost=0.00..3.78 rows=1 width=32) (actual time=0.049..0.049 rows=1 loops=119)

                        Output: (ARRAY[tbl.id])

                        ->  Seq Scan on public.tbl  (cost=0.00..5670.00 rows=1500 width=32) (actual time=0.047..0.047 rows=1 loops=119)

                              Output: ARRAY[tbl.id]

                              Filter: (array_position(cte_1.ids, tbl.id) IS NULL)

                              Rows Removed by Filter: 62

                SubPlan 2

                  ->  Limit  (cost=0.00..3.78 rows=1 width=32) (actual time=0.126..0.126 rows=1 loops=119)

                        Output: (ARRAY[tbl_1.product])

                        ->  Seq Scan on public.tbl tbl_1  (cost=0.00..5670.00 rows=1500 width=32) (actual time=0.125..0.125 rows=1 loops=119)

                              Output: ARRAY[tbl_1.product]

                              Filter: (array_position(cte_1.products, tbl_1.product) IS NULL)

                              Rows Removed by Filter: 76

                SubPlan 3

                  ->  Limit  (cost=0.00..3.78 rows=1 width=32) (actual time=0.154..0.154 rows=1 loops=119)

                        Output: (ARRAY[tbl_2.cost])

                        ->  Seq Scan on public.tbl tbl_2  (cost=0.00..5670.00 rows=1500 width=32) (actual time=0.153..0.153 rows=1 loops=119)

                              Output: ARRAY[tbl_2.cost]

                              Filter: (array_position(cte_1.costs, tbl_2.cost) IS NULL)

                              Rows Removed by Filter: 61

  ->  Sort  (cost=0.78..0.85 rows=31 width=100) (actual time=39.844..39.844 rows=1 loops=1)

        Output: cte.ids, cte.products, cte.costs, cte.i

        Sort Key: cte.i DESC

        Sort Method: top-N heapsort  Memory: 29kB

        ->  CTE Scan on cte  (cost=0.00..0.62 rows=31 width=100) (actual time=0.011..39.763 rows=120 loops=1)

              Output: cte.ids, cte.products, cte.costs, cte.i

Planning Time: 0.259 ms

Execution Time: 39.903 ms

EXPLAIN

ids
products
costs

{106,697,302,215,485,535,642,157,186,61,116,64,818,293,802,895,832,429,635,334,803,553,231,27,908,791,586,969,236,474,407,644,327,978,652,889,234,899,417,156,241,772,227,376,948,906,996,452,872,617,805,537,187,911,562,162,11,436,503,819,656,957,863,404,876,976,903,585,139,301,203,947,576,820,41,989,196,550,263,564,851,323,322,348,972,276,994,268,23,471,905,29,632,108,188,314,119,917,235,826,783,854,274,272,206,489,943,977,423,410,82,312,131,838,570,575,491,94,937,743}
{Shovel,Shirt,Bowl,Slipper,Cd,Blanket,Shawl,Doll,Flag,Lamp,Sofa,Wallet,Watch,Purse,Bag,Desk,Car,Puddle,Bread,"Ice Cube Tray",Glass,Pants,Conditioner,Stockings,Beef,Pencil,Window,Chalk,Hanger,Door,Couch,Wagon,Bananas,Sponge,"Grid Paper",Tomato,"Model Car","Chapter Book",Shoes,"Rubber Duck","Washing Machine",Drawer,Shampoo,Perfume,"Video Games",Bookmark,Rug,"Credit Card",Checkbook,"Soy Sauce Packet","Soda Can","Rusty Nail","Cinder Block",Vase,Pen,"Nail File","Key Chain","Mp3 Player",Blouse,Clothes,"Leg Warmers","Cookie Jar",Glasses,"Stop Sign",Remote,Phone,Table,Thread,Tv,Book,Bracelet,Sailboat,Carrots,Cat,Magnet,Tree,Lotion,"Sand Paper","Fake Flowers","Pool Stick",Fridge,"Air Freshener",Cup,Piano,"Tissue Box","Candy Wrapper",Balloon,Keys,"Eye Liner","Tooth Picks",Flowers,"Toe Ring","Sun Glasses",Sandal,Twister,"Sketch Pad","Usb Drive",Socks,Chocolate,Mirror,Lace,"Tire Swing",Sidewalk,Newspaper,Apple,Charger,"Cell Phone",Towel,"Boom Box","Glow Stick","Lamp Shade","Face Wash",Coasters,Monitor,Thermometer,Speakers,Eraser,"Mouse Pad",Floor,Fork}
{830.5,941.4,440.8,209.7,917.1,690.8,526.6,542.9,833.9,600.5,4.7,421.7,382.6,997.4,504.1,37.3,356.0,832.3,92.0,131.4,78.5,151.1,701.0,173.6,75.6,833.1,476.9,731.5,189.5,400.4,268.3,788.1,427.6,172.5,553.0,118.2,951.5,430.7,401.0,924.6,415.8,633.0,365.5,511.3,430.8,532.6,905.6,913.3,682.5,674.1,998.3,47.9,784.4,874.6,638.6,462.4,997.3,825.7,610.8,471.2,89.1,579.2,560.5,995.4,210.9,945.1,771.6,364.1,618.8,264.2,144.8,148.1,884.5,751.2,661.4,89.6,489.0,646.1,888.6,596.6,654.7,153.7,334.6,824.7,393.7,108.2,542.5,361.9,895.1,308.8,765.6,503.7,167.3,355.4,945.5,196.4,111.8,20.7,647.4,18.8,181.1,939.7,121.5,393.6,637.3,638.3,355.1,881.8,465.3,317.1,810.7,875.9,194.1,827.5,969.5,754.0,389.2,213.9,64.3,362.5}

SELECT 1

--this will pop the top value from each index
--then keep iteratively skipping over to the 2nd, 3rd and so on
prepare recursive_cte_3way_index_skip_scan(int) as 
with recursive cte as (
  select   1 as i
          , array[min(id)] ids 
          , array[min(product)] products
          , array[min(cost)] costs
  from tbl --pop 1 value from each index
  union all
  select cte.i+1 as i
       ,  (select array[min(id)] ids 
           from tbl 
           where tbl.id>cte.ids[1]) ||cte.ids
       ,  (select array[min(product)] products 
           from tbl 
           where tbl.product>cte.products[1]) ||cte.products
       ,  (select array[min(cost)] costs 
           from tbl 
           where tbl.cost>cte.costs[1]) ||cte.costs
  from cte where $1>i
) 
select ids, products, costs
from cte order by i desc limit 1;

explain analyze verbose execute recursive_cte_3way_index_skip_scan(3);
execute recursive_cte_3way_index_skip_scan(3);

explain analyze verbose execute recursive_cte_3way_index_skip_scan(30);
execute recursive_cte_3way_index_skip_scan(30);

explain analyze verbose execute recursive_cte_3way_index_skip_scan(120);
execute recursive_cte_3way_index_skip_scan(120);

PREPARE

QUERY PLAN

Limit  (cost=46.43..46.43 rows=1 width=100) (actual time=0.195..0.198 rows=1 loops=1)

  Output: cte.ids, cte.products, cte.costs, cte.i

  CTE cte

    ->  Recursive Union  (cost=1.34..45.65 rows=31 width=100) (actual time=0.061..0.186 rows=3 loops=1)

          ->  Result  (cost=1.34..1.35 rows=1 width=100) (actual time=0.060..0.061 rows=1 loops=1)

                Output: 1, ARRAY[$1], ARRAY[$2], ARRAY[$3]

                InitPlan 7 (returns $1)

                  ->  Limit  (cost=0.42..0.44 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1)

                        Output: tbl_3.id

                        ->  Index Only Scan using idx1 on public.tbl tbl_3  (cost=0.42..6474.42 rows=300000 width=4) (actual time=0.012..0.012 rows=1 loops=1)

                              Output: tbl_3.id

                              Index Cond: (tbl_3.id IS NOT NULL)

                              Heap Fetches: 0

                InitPlan 8 (returns $2)

                  ->  Limit  (cost=0.42..0.44 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)

                        Output: tbl_4.product

                        ->  Index Only Scan using idx2 on public.tbl tbl_4  (cost=0.42..6522.42 rows=300000 width=8) (actual time=0.007..0.007 rows=1 loops=1)

                              Output: tbl_4.product

                              Index Cond: (tbl_4.product IS NOT NULL)

                              Heap Fetches: 0

                InitPlan 9 (returns $3)

                  ->  Limit  (cost=0.42..0.45 rows=1 width=6) (actual time=0.033..0.033 rows=1 loops=1)

                        Output: tbl_5.cost

                        ->  Index Only Scan using idx3 on public.tbl tbl_5  (cost=0.42..8750.42 rows=300000 width=6) (actual time=0.033..0.033 rows=1 loops=1)

                              Output: tbl_5.cost

                              Index Cond: (tbl_5.cost IS NOT NULL)

                              Heap Fetches: 0

          ->  WorkTable Scan on cte cte_1  (cost=0.00..4.40 rows=3 width=100) (actual time=0.039..0.040 rows=1 loops=3)

                Output: (cte_1.i + 1), ((SubPlan 2) || cte_1.ids), ((SubPlan 4) || cte_1.products), ((SubPlan 6) || cte_1.costs)

                Filter: (3 > cte_1.i)

                Rows Removed by Filter: 0

                SubPlan 2

                  ->  Result  (cost=0.45..0.46 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=2)

                        Output: ARRAY[$5]

                        InitPlan 1 (returns $5)

                          ->  Limit  (cost=0.42..0.45 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=2)

                                Output: tbl.id

                                ->  Index Only Scan using idx1 on public.tbl  (cost=0.42..2540.42 rows=100000 width=4) (actual time=0.007..0.007 rows=1 loops=2)

                                      Output: tbl.id

                                      Index Cond: ((tbl.id IS NOT NULL) AND (tbl.id > (cte_1.ids)[1]))

                                      Heap Fetches: 0

                SubPlan 4

                  ->  Result  (cost=0.45..0.46 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=2)

                        Output: ARRAY[$7]

                        InitPlan 3 (returns $7)

                          ->  Limit  (cost=0.42..0.45 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=2)

                                Output: tbl_1.product

                                ->  Index Only Scan using idx2 on public.tbl tbl_1  (cost=0.42..2556.42 rows=100000 width=8) (actual time=0.008..0.008 rows=1 loops=2)

                                      Output: tbl_1.product

                                      Index Cond: ((tbl_1.product IS NOT NULL) AND (tbl_1.product > (cte_1.products)[1]))

                                      Heap Fetches: 0

                SubPlan 6

                  ->  Result  (cost=0.46..0.47 rows=1 width=32) (actual time=0.031..0.031 rows=1 loops=2)

                        Output: ARRAY[$9]

                        InitPlan 5 (returns $9)

                          ->  Limit  (cost=0.42..0.46 rows=1 width=6) (actual time=0.030..0.030 rows=1 loops=2)

                                Output: tbl_2.cost

                                ->  Index Only Scan using idx3 on public.tbl tbl_2  (cost=0.42..3300.42 rows=100000 width=6) (actual time=0.030..0.030 rows=1 loops=2)

                                      Output: tbl_2.cost

                                      Index Cond: ((tbl_2.cost IS NOT NULL) AND (tbl_2.cost > (cte_1.costs)[1]))

                                      Heap Fetches: 0

  ->  Sort  (cost=0.78..0.85 rows=31 width=100) (actual time=0.195..0.195 rows=1 loops=1)

        Output: cte.ids, cte.products, cte.costs, cte.i

        Sort Key: cte.i DESC

        Sort Method: top-N heapsort  Memory: 25kB

        ->  CTE Scan on cte  (cost=0.00..0.62 rows=31 width=100) (actual time=0.064..0.188 rows=3 loops=1)

              Output: cte.ids, cte.products, cte.costs, cte.i

Planning Time: 0.606 ms

Execution Time: 0.283 ms

EXPLAIN

ids
products
costs

{2,1,0}
{Bag,Apple,"Air Freshener"}
{0.2,0.1,0.0}

SELECT 1

QUERY PLAN

Limit  (cost=46.43..46.43 rows=1 width=100) (actual time=1.269..1.272 rows=1 loops=1)

  Output: cte.ids, cte.products, cte.costs, cte.i

  CTE cte

    ->  Recursive Union  (cost=1.34..45.65 rows=31 width=100) (actual time=0.034..1.227 rows=30 loops=1)

          ->  Result  (cost=1.34..1.35 rows=1 width=100) (actual time=0.034..0.035 rows=1 loops=1)

                Output: 1, ARRAY[$1], ARRAY[$2], ARRAY[$3]

                InitPlan 7 (returns $1)

                  ->  Limit  (cost=0.42..0.44 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)

                        Output: tbl_3.id

                        ->  Index Only Scan using idx1 on public.tbl tbl_3  (cost=0.42..6474.42 rows=300000 width=4) (actual time=0.009..0.009 rows=1 loops=1)

                              Output: tbl_3.id

                              Index Cond: (tbl_3.id IS NOT NULL)

                              Heap Fetches: 0

                InitPlan 8 (returns $2)

                  ->  Limit  (cost=0.42..0.44 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)

                        Output: tbl_4.product

                        ->  Index Only Scan using idx2 on public.tbl tbl_4  (cost=0.42..6522.42 rows=300000 width=8) (actual time=0.006..0.006 rows=1 loops=1)

                              Output: tbl_4.product

                              Index Cond: (tbl_4.product IS NOT NULL)

                              Heap Fetches: 0

                InitPlan 9 (returns $3)

                  ->  Limit  (cost=0.42..0.45 rows=1 width=6) (actual time=0.014..0.014 rows=1 loops=1)

                        Output: tbl_5.cost

                        ->  Index Only Scan using idx3 on public.tbl tbl_5  (cost=0.42..8750.42 rows=300000 width=6) (actual time=0.014..0.014 rows=1 loops=1)

                              Output: tbl_5.cost

                              Index Cond: (tbl_5.cost IS NOT NULL)

                              Heap Fetches: 0

          ->  WorkTable Scan on cte cte_1  (cost=0.00..4.40 rows=3 width=100) (actual time=0.038..0.039 rows=1 loops=30)

                Output: (cte_1.i + 1), ((SubPlan 2) || cte_1.ids), ((SubPlan 4) || cte_1.products), ((SubPlan 6) || cte_1.costs)

                Filter: (30 > cte_1.i)

                Rows Removed by Filter: 0

                SubPlan 2

                  ->  Result  (cost=0.45..0.46 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=29)

                        Output: ARRAY[$5]

                        InitPlan 1 (returns $5)

                          ->  Limit  (cost=0.42..0.45 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=29)

                                Output: tbl.id

                                ->  Index Only Scan using idx1 on public.tbl  (cost=0.42..2540.42 rows=100000 width=4) (actual time=0.004..0.004 rows=1 loops=29)

                                      Output: tbl.id

                                      Index Cond: ((tbl.id IS NOT NULL) AND (tbl.id > (cte_1.ids)[1]))

                                      Heap Fetches: 0

                SubPlan 4

                  ->  Result  (cost=0.45..0.46 rows=1 width=32) (actual time=0.007..0.007 rows=1 loops=29)

                        Output: ARRAY[$7]

                        InitPlan 3 (returns $7)

                          ->  Limit  (cost=0.42..0.45 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=29)

                                Output: tbl_1.product

                                ->  Index Only Scan using idx2 on public.tbl tbl_1  (cost=0.42..2556.42 rows=100000 width=8) (actual time=0.006..0.006 rows=1 loops=29)

                                      Output: tbl_1.product

                                      Index Cond: ((tbl_1.product IS NOT NULL) AND (tbl_1.product > (cte_1.products)[1]))

                                      Heap Fetches: 0

                SubPlan 6

                  ->  Result  (cost=0.46..0.47 rows=1 width=32) (actual time=0.023..0.023 rows=1 loops=29)

                        Output: ARRAY[$9]

                        InitPlan 5 (returns $9)

                          ->  Limit  (cost=0.42..0.46 rows=1 width=6) (actual time=0.022..0.022 rows=1 loops=29)

                                Output: tbl_2.cost

                                ->  Index Only Scan using idx3 on public.tbl tbl_2  (cost=0.42..3300.42 rows=100000 width=6) (actual time=0.022..0.022 rows=1 loops=29)

                                      Output: tbl_2.cost

                                      Index Cond: ((tbl_2.cost IS NOT NULL) AND (tbl_2.cost > (cte_1.costs)[1]))

                                      Heap Fetches: 0

  ->  Sort  (cost=0.78..0.85 rows=31 width=100) (actual time=1.268..1.269 rows=1 loops=1)

        Output: cte.ids, cte.products, cte.costs, cte.i

        Sort Key: cte.i DESC

        Sort Method: top-N heapsort  Memory: 26kB

        ->  CTE Scan on cte  (cost=0.00..0.62 rows=31 width=100) (actual time=0.036..1.249 rows=30 loops=1)

              Output: cte.ids, cte.products, cte.costs, cte.i

Planning Time: 0.475 ms

Execution Time: 1.321 ms

EXPLAIN

ids
products
costs

{29,28,27,26,25,24,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1,0}
{Cd,Cat,Carrots,Car,Canvas,"Candy Wrapper",Candle,Camera,Button,Buckle,Brocolli,Bread,Bracelet,Box,Bowl,Bow,"Bottle Cap",Bottle,"Boom Box",Bookmark,Book,Blouse,Blanket,Beef,Bed,Bananas,Balloon,Bag,Apple,"Air Freshener"}
{2.9,2.8,2.7,2.6,2.5,2.4,2.3,2.2,2.1,2.0,1.9,1.8,1.7,1.6,1.5,1.4,1.3,1.2,1.1,1.0,0.9,0.8,0.7,0.6,0.5,0.4,0.3,0.2,0.1,0.0}

SELECT 1

QUERY PLAN

Limit  (cost=46.43..46.43 rows=1 width=100) (actual time=4.847..4.852 rows=1 loops=1)

  Output: cte.ids, cte.products, cte.costs, cte.i

  CTE cte

    ->  Recursive Union  (cost=1.34..45.65 rows=31 width=100) (actual time=0.033..4.648 rows=120 loops=1)

          ->  Result  (cost=1.34..1.35 rows=1 width=100) (actual time=0.032..0.034 rows=1 loops=1)

                Output: 1, ARRAY[$1], ARRAY[$2], ARRAY[$3]

                InitPlan 7 (returns $1)

                  ->  Limit  (cost=0.42..0.44 rows=1 width=4) (actual time=0.009..0.010 rows=1 loops=1)

                        Output: tbl_3.id

                        ->  Index Only Scan using idx1 on public.tbl tbl_3  (cost=0.42..6474.42 rows=300000 width=4) (actual time=0.009..0.009 rows=1 loops=1)

                              Output: tbl_3.id

                              Index Cond: (tbl_3.id IS NOT NULL)

                              Heap Fetches: 0

                InitPlan 8 (returns $2)

                  ->  Limit  (cost=0.42..0.44 rows=1 width=8) (actual time=0.005..0.006 rows=1 loops=1)

                        Output: tbl_4.product

                        ->  Index Only Scan using idx2 on public.tbl tbl_4  (cost=0.42..6522.42 rows=300000 width=8) (actual time=0.005..0.005 rows=1 loops=1)

                              Output: tbl_4.product

                              Index Cond: (tbl_4.product IS NOT NULL)

                              Heap Fetches: 0

                InitPlan 9 (returns $3)

                  ->  Limit  (cost=0.42..0.45 rows=1 width=6) (actual time=0.013..0.014 rows=1 loops=1)

                        Output: tbl_5.cost

                        ->  Index Only Scan using idx3 on public.tbl tbl_5  (cost=0.42..8750.42 rows=300000 width=6) (actual time=0.013..0.013 rows=1 loops=1)

                              Output: tbl_5.cost

                              Index Cond: (tbl_5.cost IS NOT NULL)

                              Heap Fetches: 0

          ->  WorkTable Scan on cte cte_1  (cost=0.00..4.40 rows=3 width=100) (actual time=0.037..0.037 rows=1 loops=120)

                Output: (cte_1.i + 1), ((SubPlan 2) || cte_1.ids), ((SubPlan 4) || cte_1.products), ((SubPlan 6) || cte_1.costs)

                Filter: (120 > cte_1.i)

                Rows Removed by Filter: 0

                SubPlan 2

                  ->  Result  (cost=0.45..0.46 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=119)

                        Output: ARRAY[$5]

                        InitPlan 1 (returns $5)

                          ->  Limit  (cost=0.42..0.45 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=119)

                                Output: tbl.id

                                ->  Index Only Scan using idx1 on public.tbl  (cost=0.42..2540.42 rows=100000 width=4) (actual time=0.004..0.004 rows=1 loops=119)

                                      Output: tbl.id

                                      Index Cond: ((tbl.id IS NOT NULL) AND (tbl.id > (cte_1.ids)[1]))

                                      Heap Fetches: 0

                SubPlan 4

                  ->  Result  (cost=0.45..0.46 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=119)

                        Output: ARRAY[$7]

                        InitPlan 3 (returns $7)

                          ->  Limit  (cost=0.42..0.45 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=119)

                                Output: tbl_1.product

                                ->  Index Only Scan using idx2 on public.tbl tbl_1  (cost=0.42..2556.42 rows=100000 width=8) (actual time=0.007..0.007 rows=1 loops=119)

                                      Output: tbl_1.product

                                      Index Cond: ((tbl_1.product IS NOT NULL) AND (tbl_1.product > (cte_1.products)[1]))

                                      Heap Fetches: 0

                SubPlan 6

                  ->  Result  (cost=0.46..0.47 rows=1 width=32) (actual time=0.020..0.020 rows=1 loops=119)

                        Output: ARRAY[$9]

                        InitPlan 5 (returns $9)

                          ->  Limit  (cost=0.42..0.46 rows=1 width=6) (actual time=0.019..0.019 rows=1 loops=119)

                                Output: tbl_2.cost

                                ->  Index Only Scan using idx3 on public.tbl tbl_2  (cost=0.42..3300.42 rows=100000 width=6) (actual time=0.019..0.019 rows=1 loops=119)

                                      Output: tbl_2.cost

                                      Index Cond: ((tbl_2.cost IS NOT NULL) AND (tbl_2.cost > (cte_1.costs)[1]))

                                      Heap Fetches: 0

  ->  Sort  (cost=0.78..0.85 rows=31 width=100) (actual time=4.847..4.847 rows=1 loops=1)

        Output: cte.ids, cte.products, cte.costs, cte.i

        Sort Key: cte.i DESC

        Sort Method: top-N heapsort  Memory: 29kB

        ->  CTE Scan on cte  (cost=0.00..0.62 rows=31 width=100) (actual time=0.034..4.769 rows=120 loops=1)

              Output: cte.ids, cte.products, cte.costs, cte.i

Planning Time: 0.410 ms

Execution Time: 4.908 ms

EXPLAIN

ids
products
costs

{119,118,117,116,115,114,113,112,111,110,109,108,107,106,105,104,103,102,101,100,99,98,97,96,95,94,93,92,91,90,89,88,87,86,85,84,83,82,81,80,79,78,77,76,75,74,73,72,71,70,69,68,67,66,65,64,63,62,61,60,59,58,57,56,55,54,53,52,51,50,49,48,47,46,45,44,43,42,41,40,39,38,37,36,35,34,33,32,31,30,29,28,27,26,25,24,23,22,21,20,19,18,17,16,15,14,13,12,11,10,9,8,7,6,5,4,3,2,1,0}
{"Pool Stick","Playing Card",Plate,"Plastic Fork",Pillow,"Picture Frame",Piano,"Photo Album",Phone,Perfume,Pencil,Pen,Paper,Pants,"Paint Brush","Packing Peanuts",Outlet,Newspaper,Needle,"Nail File","Nail Clippers","Mp3 Player","Mouse Pad",Mop,Monitor,Money,"Model Car",Mirror,Milk,Magnet,Lotion,"Lip Gloss","Leg Warmers","Lamp Shade",Lamp,Lace,Knife,Keys,Keyboard,"Key Chain",Ipod,"Ice Cube Tray",House,Helmet,Headphones,Hanger,"Hair Tie","Hair Brush","Grid Paper","Greeting Card","Glow Stick",Glasses,Glass,Fridge,Fork,Food,Flowers,Floor,Flag,"Fake Flowers","Face Wash","Eye Liner",Eraser,"Drill Press",Drawer,Door,Doll,Desk,Deodorant,Cup,"Credit Card",Couch,Cork,"Cookie Jar",Controller,Conditioner,Computer,Coasters,Clothes,Clock,"Clay Pot",Clamp,"Cinder Block",Chocolate,Checkbook,Charger,"Chapter Book",Chalk,Chair,"Cell Phone",Cd,Cat,Carrots,Car,Canvas,"Candy Wrapper",Candle,Camera,Button,Buckle,Brocolli,Bread,Bracelet,Box,Bowl,Bow,"Bottle Cap",Bottle,"Boom Box",Bookmark,Book,Blouse,Blanket,Beef,Bed,Bananas,Balloon,Bag,Apple,"Air Freshener"}
{11.9,11.8,11.7,11.6,11.5,11.4,11.3,11.2,11.1,11.0,10.9,10.8,10.7,10.6,10.5,10.4,10.3,10.2,10.1,10.0,9.9,9.8,9.7,9.6,9.5,9.4,9.3,9.2,9.1,9.0,8.9,8.8,8.7,8.6,8.5,8.4,8.3,8.2,8.1,8.0,7.9,7.8,7.7,7.6,7.5,7.4,7.3,7.2,7.1,7.0,6.9,6.8,6.7,6.6,6.5,6.4,6.3,6.2,6.1,6.0,5.9,5.8,5.7,5.6,5.5,5.4,5.3,5.2,5.1,5.0,4.9,4.8,4.7,4.6,4.5,4.4,4.3,4.2,4.1,4.0,3.9,3.8,3.7,3.6,3.5,3.4,3.3,3.2,3.1,3.0,2.9,2.8,2.7,2.6,2.5,2.4,2.3,2.2,2.1,2.0,1.9,1.8,1.7,1.6,1.5,1.4,1.3,1.2,1.1,1.0,0.9,0.8,0.7,0.6,0.5,0.4,0.3,0.2,0.1,0.0}

SELECT 1

--risky but quick
--the slice provided by tablesample system(.1) 
--might happen to not offer 3 different values in it
prepare pop_3_from_array_tablesample as
SELECT 
    (ARRAY_AGG(DISTINCT id))[:3],
    (ARRAY_AGG(DISTINCT product))[:3],
    (ARRAY_AGG(DISTINCT cost))[:3]
FROM tbl tablesample system(.1) ;

explain analyze verbose execute pop_3_from_array_tablesample;
execute pop_3_from_array_tablesample;

PREPARE

QUERY PLAN

Aggregate  (cost=26.35..26.36 rows=1 width=96) (actual time=0.029..0.030 rows=1 loops=1)

  Output: (array_agg(DISTINCT id))[:3], (array_agg(DISTINCT product))[:3], (array_agg(DISTINCT cost))[:3]

  ->  Sort  (cost=23.34..24.09 rows=300 width=18) (actual time=0.021..0.021 rows=0 loops=1)

        Output: id, product, cost

        Sort Key: tbl.id

        Sort Method: quicksort  Memory: 25kB

        ->  Sample Scan on public.tbl  (cost=0.00..11.00 rows=300 width=18) (actual time=0.018..0.018 rows=0 loops=1)

              Output: id, product, cost

              Sampling: system ('0.1'::real)

Planning Time: 0.104 ms

Execution Time: 0.054 ms

EXPLAIN

array_agg
array_agg
array_agg

null
null
null

SELECT 1

prepare percentile_discs_no_index as 
SELECT percentile_disc('{0,.5,1}'::float[]) WITHIN GROUP (ORDER BY id)      AS pctl_id
     , percentile_disc('{0,.5,1}'::float[]) WITHIN GROUP (ORDER BY product) AS pctl_product   
     , percentile_disc('{0,.5,1}'::float[]) WITHIN GROUP (ORDER BY cost)    AS pctl_cost
FROM   tbl;

explain analyze verbose execute percentile_discs_no_index;
execute percentile_discs_no_index;

PREPARE

QUERY PLAN

Aggregate  (cost=7170.01..7170.02 rows=1 width=96) (actual time=443.041..443.042 rows=1 loops=1)

  Output: percentile_disc('{0,0.5,1}'::double precision[]) WITHIN GROUP (ORDER BY id), percentile_disc('{0,0.5,1}'::double precision[]) WITHIN GROUP (ORDER BY product), percentile_disc('{0,0.5,1}'::double precision[]) WITHIN GROUP (ORDER BY cost)

  ->  Seq Scan on public.tbl  (cost=0.00..4920.00 rows=300000 width=18) (actual time=0.006..22.911 rows=300000 loops=1)

        Output: id, product, cost

Planning Time: 0.065 ms

Execution Time: 444.817 ms

EXPLAIN

pctl_id
pctl_product
pctl_cost

{0,503,1000}
{"Air Freshener",Money,Zipper}
{0.0,500.5,1000.0}

SELECT 1

prepare percentile_discs_no_index_tablesample as 
SELECT percentile_disc('{0,.5,1}'::float[]) WITHIN GROUP (ORDER BY id)      AS pctl_id
     , percentile_disc('{0,.5,1}'::float[]) WITHIN GROUP (ORDER BY product) AS pctl_product   
     , percentile_disc('{0,.5,1}'::float[]) WITHIN GROUP (ORDER BY cost)    AS pctl_cost
FROM   tbl tablesample system(1);

explain analyze verbose execute percentile_discs_no_index_tablesample;
execute percentile_discs_no_index_tablesample;

PREPARE

QUERY PLAN

Aggregate  (cost=128.51..128.52 rows=1 width=96) (actual time=3.385..3.386 rows=1 loops=1)

  Output: percentile_disc('{0,0.5,1}'::double precision[]) WITHIN GROUP (ORDER BY id), percentile_disc('{0,0.5,1}'::double precision[]) WITHIN GROUP (ORDER BY product), percentile_disc('{0,0.5,1}'::double precision[]) WITHIN GROUP (ORDER BY cost)

  ->  Sample Scan on public.tbl  (cost=0.00..106.00 rows=3000 width=18) (actual time=0.009..0.447 rows=3530 loops=1)

        Output: id, product, cost

        Sampling: system ('1'::real)

Planning Time: 0.080 ms

Execution Time: 3.409 ms

EXPLAIN

pctl_id
pctl_product
pctl_cost

{0,514,1000}
{"Air Freshener",Milk,Zipper}
{0.7,505.6,999.8}

SELECT 1
id	product	cost
802	Grid Paper	504.1
811	Shovel	685.7
831	Mouse Pad	800.3
888	Cup	903.0
443	Fridge	578.0
135	Brocolli	458.2
809	Bread	210.0
16	Vase	56.4