Re: join from array or cursor

On Aug 21, 2009, at 9:22 AM, Greg Stark wrote:

> Of course immediately upon hitting send I did think of a way:
>
> SELECT (r).*
>  FROM (SELECT (SELECT x FROM x WHERE a=id) AS r
>          FROM unnest(array[1,2]) AS arr(id)
>       ) AS subq;

Thanks to all for the interesting insights and discussion. Where in
the docs can I learn about writing queries like that :).

While it avoids the sort of my method, it appears to be almost 5 times
slower (about 4000 keys in the cursor, Postgres 8.4.0):

EXPLAIN ANALYZE SELECT (r).*
  FROM (SELECT (SELECT "work" FROM "work" WHERE dbid=id) AS r
          FROM cursor_pk('c1') AS arr(id)
       ) AS subq;


Function Scan on cursor_pk arr  (cost=0.00..116011.72 rows=1000
width=4) (actual time=13.561..249.916 rows=4308 loops=1)
   SubPlan 1
     ->  Index Scan using work_pkey on work  (cost=0.00..8.27 rows=1
width=32) (actual time=0.003..0.003 rows=1 loops=4308)
           Index Cond: (dbid = $0)
   SubPlan 2
     ->  Index Scan using work_pkey on work  (cost=0.00..8.27 rows=1
width=32) (actual time=0.002..0.002 rows=1 loops=4308)
           Index Cond: (dbid = $0)
   SubPlan 3
     ->  Index Scan using work_pkey on work  (cost=0.00..8.27 rows=1
width=32) (actual time=0.002..0.002 rows=1 loops=4308)
           Index Cond: (dbid = $0)
   SubPlan 4
     ->  Index Scan using work_pkey on work  (cost=0.00..8.27 rows=1
width=32) (actual time=0.002..0.002 rows=1 loops=4308)
           Index Cond: (dbid = $0)
   SubPlan 5
     ->  Index Scan using work_pkey on work  (cost=0.00..8.27 rows=1
width=32) (actual time=0.002..0.002 rows=1 loops=4308)
           Index Cond: (dbid = $0)
   SubPlan 6
     ->  Index Scan using work_pkey on work  (cost=0.00..8.27 rows=1
width=32) (actual time=0.002..0.002 rows=1 loops=4308)
           Index Cond: (dbid = $0)
   SubPlan 7
     ->  Index Scan using work_pkey on work  (cost=0.00..8.27 rows=1
width=32) (actual time=0.002..0.002 rows=1 loops=4308)
           Index Cond: (dbid = $0)
   SubPlan 8
     ->  Index Scan using work_pkey on work  (cost=0.00..8.27 rows=1
width=32) (actual time=0.002..0.002 rows=1 loops=4308)
           Index Cond: (dbid = $0)
   SubPlan 9
     ->  Index Scan using work_pkey on work  (cost=0.00..8.27 rows=1
width=32) (actual time=0.002..0.002 rows=1 loops=4308)
           Index Cond: (dbid = $0)
   SubPlan 10
     ->  Index Scan using work_pkey on work  (cost=0.00..8.27 rows=1
width=32) (actual time=0.002..0.002 rows=1 loops=4308)
           Index Cond: (dbid = $0)
   SubPlan 11
     ->  Index Scan using work_pkey on work  (cost=0.00..8.27 rows=1
width=32) (actual time=0.002..0.002 rows=1 loops=4308)
           Index Cond: (dbid = $0)
   SubPlan 12
     ->  Index Scan using work_pkey on work  (cost=0.00..8.27 rows=1
width=32) (actual time=0.002..0.002 rows=1 loops=4308)
           Index Cond: (dbid = $0)
   SubPlan 13
     ->  Index Scan using work_pkey on work  (cost=0.00..8.27 rows=1
width=32) (actual time=0.002..0.002 rows=1 loops=4308)
           Index Cond: (dbid = $0)
   SubPlan 14
     ->  Index Scan using work_pkey on work  (cost=0.00..8.27 rows=1
width=32) (actual time=0.002..0.002 rows=1 loops=4308)
           Index Cond: (dbid = $0)
Total runtime: 250.739 ms



EXPLAIN ANALYZE SELECT * FROM cursor_pk('c1') c LEFT JOIN "work" ON
(c.pk = "work".dbid) order by c.idx;

Sort  (cost=771.23..773.73 rows=1000 width=375) (actual
time=36.058..38.392 rows=4308 loops=1)
   Sort Key: c.idx
   Sort Method:  external merge  Disk: 1656kB
   ->  Merge Right Join  (cost=309.83..721.40 rows=1000 width=375)
(actual time=15.447..22.293 rows=4308 loops=1)
         Merge Cond: (work.dbid = c.pk)
         ->  Index Scan using work_pkey on work  (cost=0.00..385.80
rows=4308 width=367) (actual time=0.020..2.078 rows=4308 loops=1)
         ->  Sort  (cost=309.83..312.33 rows=1000 width=8) (actual
time=15.420..15.946 rows=4308 loops=1)
               Sort Key: c.pk
               Sort Method:  quicksort  Memory: 297kB
               ->  Function Scan on cursor_pk_order c
(cost=0.00..260.00 rows=1000 width=8) (actual time=12.672..13.073
rows=4308 loops=1)
Total runtime: 51.886 ms


Thanks for any further suggestions.



John DeSoi, Ph.D.