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.