Re: somewhat slow query with subselect

Tom Lane wrote:
> Marcus Engene <mengpg2(at)engene(dot)se> writes:
>
>> ... In a case with some 5000 rows belonging to owner 123, this select really
>> takes a long time. Way longer than without the subselect and order by
>> filelength. I agree that with the subselect it would take some extra
>> juice, but in my mind it would do some hash in memory which wouldn't be
>> too slow to lookup in.
>>
>
> 8.4 can turn EXISTS subqueries into hash joins, but previous releases
> won't...
>
> regards, tom lane
>
>
Thank you very much for your answer, Tom. I tried to join the table
instead and it was way faster.

Sort (cost=46769.87..46770.51 rows=258 width=48)
Sort Key: pic.filesize
-> Nested Loop (cost=34.30..46759.54 rows=258 width=48)
Join Filter: ((picsame.objectid <> pic.objectid) AND
(pic.filesize = picsame.filesize))
-> Nested Loop (cost=8.27..3099.28 rows=16 width=56)
-> HashAggregate (cost=8.27..8.28 rows=1 width=4)
-> Index Scan using user_c2 on user pu2
(cost=0.00..8.27 rows=1 width=4)
Index Cond: ((username_locase)::text =
'prolificarts'::text)
-> Index Scan using item_common_x1 on item_common pic
(cost=0.00..3081.41 rows=767 width=52)
Index Cond: (pic.user = pu2.objectid)
-> Bitmap Heap Scan on item_common picsame
(cost=26.03..2715.34 rows=767 width=16)
Recheck Cond: (picsame.user = pic.user)
-> Bitmap Index Scan on item_common_x1
(cost=0.00..25.84 rows=767 width=0)
Index Cond: (picsame.user = pic.user)

Best regards,
Marcus