On Tue, Apr 6, 2010 at 8:42 PM, norn <andrey.perliev@xxxxxxxxx> wrote: > I have some mysterious slow downs with ORDER BY and LIMIT. When LIMIT > getting greater than some value (greater than 3 in my case), query > takes 4-5 secs instead of 0.25ms. All of the necessary indexes are in > place. I have no idea what to do, so any advices are welcome! > > Here my queries and explain analyzes; > > First Query with LIMIT 3 (fast) > ------------- > explain analyze SELECT core_object.id from "core_object" INNER JOIN > "plugins_plugin_addr" ON ("core_object"."id" = > "plugins_plugin_addr"."oid_id") INNER JOIN "plugins_guide_address" ON > ("plugins_plugin_addr"."address_id" = "plugins_guide_address"."id") > WHERE "plugins_guide_address"."city_id" = 4535 ORDER BY > "core_object"."id" DESC LIMIT 3; > > Limit (cost=0.00..9.57 rows=3 width=4) (actual time=0.090..0.138 > rows=3 loops=1) > -> Merge Join (cost=0.00..1098182.56 rows=344125 width=4) (actual > time=0.088..0.136 rows=3 loops=1) > Merge Cond: (plugins_plugin_addr.oid_id = core_object.id) > -> Nested Loop (cost=0.00..972804.02 rows=344125 width=4) > (actual time=0.056..0.095 rows=3 loops=1) > -> Index Scan Backward using > plugins_plugin_addr_oid_id on plugins_plugin_addr > (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..0.032 > rows=3 loops=1) > -> Index Scan using plugins_guide_address_pkey on > plugins_guide_address (cost=0.00..0.56 rows=1 width=4) (actual > time=0.017..0.018 rows=1 loops=3) > Index Cond: (plugins_guide_address.id = > plugins_plugin_addr.address_id) > Filter: (plugins_guide_address.city_id = 4535) > -> Index Scan using core_object_pkey_desc on core_object > (cost=0.00..113516.08 rows=3091134 width=4) (actual time=0.026..0.028 > rows=3 loops=1) > Total runtime: 0.244 ms > (10 rows) > > Second Query, the same, but with LIMIT 4 (slooooow) > ------------- > explain analyze SELECT core_object.id from "core_object" INNER JOIN > "plugins_plugin_addr" ON ("core_object"."id" = > "plugins_plugin_addr"."oid_id") INNER JOIN "plugins_guide_address" ON > ("plugins_plugin_addr"."address_id" = "plugins_guide_address"."id") > WHERE "plugins_guide_address"."city_id" = 4535 ORDER BY > "core_object"."id" DESC LIMIT 4; > > Limit (cost=0.00..12.76 rows=4 width=4) (actual time=0.091..4436.795 > rows=4 loops=1) > -> Merge Join (cost=0.00..1098182.56 rows=344125 width=4) (actual > time=0.089..4436.791 rows=4 loops=1) > Merge Cond: (plugins_plugin_addr.oid_id = core_object.id) > -> Nested Loop (cost=0.00..972804.02 rows=344125 width=4) > (actual time=0.056..3988.249 rows=4 loops=1) > -> Index Scan Backward using > plugins_plugin_addr_oid_id on plugins_plugin_addr > (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..329.942 > rows=1244476 loops=1) > -> Index Scan using plugins_guide_address_pkey on > plugins_guide_address (cost=0.00..0.56 rows=1 width=4) (actual > time=0.003..0.003 rows=0 loops=1244476) > Index Cond: (plugins_guide_address.id = > plugins_plugin_addr.address_id) > Filter: (plugins_guide_address.city_id = 4535) > -> Index Scan using core_object_pkey_desc on core_object > (cost=0.00..113516.08 rows=3091134 width=4) (actual > time=0.027..284.195 rows=1244479 loops=1) > Total runtime: 4436.894 ms > (10 rows) What do you get with no LIMIT at all? ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance