Tom Lane wrote:
Marcus Engene <mengpg2@xxxxxxxxx> 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
