Re: significant slow down with various LIMIT

Kevin,
thanks for your time!
Here the requested tests.

> (1) Try it without the ORDER BY clause and the LIMIT.
W/o the 'order by' it works instantly (about 1ms!)
Limit (cost=0.00..3.59 rows=5 width=4) (actual time=0.127..0.229
rows=5 loops=1)
-> Nested Loop (cost=0.00..277863.53 rows=386544 width=4) (actual
time=0.125..0.224 rows=5 loops=1)
-> Nested Loop (cost=0.00..91136.78 rows=386544 width=4)
(actual time=0.106..0.154 rows=5 loops=1)
-> Index Scan using plugins_guide_address_city_id on
plugins_guide_address (cost=0.00..41109.07 rows=27673 width=4)
(actual time=0.068..0.080 rows=5 loops=1)
Index Cond: (city_id = 4535)
-> Index Scan using plugins_plugin_addr_address_id on
plugins_plugin_addr (cost=0.00..1.63 rows=14 width=8) (actual
time=0.011..0.012 rows=1 loops=5)
Index Cond: (plugins_plugin_addr.address_id =
plugins_guide_address.id)
-> Index Scan using core_object_pkey on core_object
(cost=0.00..0.47 rows=1 width=4) (actual time=0.011..0.012 rows=1
loops=5)
Index Cond: (core_object.id =
plugins_plugin_addr.oid_id)
Total runtime: 0.328 ms
(10 rows)

W/o the limit it takes 1.4 seconds, which is anyway better than...
Sort (cost=199651.74..200618.10 rows=386544 width=4) (actual
time=1153.167..1157.841 rows=43898 loops=1)
Sort Key: core_object.id
Sort Method: quicksort Memory: 3594kB
-> Hash Join (cost=81234.35..163779.93 rows=386544 width=4)
(actual time=122.050..1128.909 rows=43898 loops=1)
Hash Cond: (core_object.id = plugins_plugin_addr.oid_id)
-> Seq Scan on core_object (cost=0.00..46467.07
rows=3221307 width=4) (actual time=0.011..378.677 rows=3221349
loops=1)
-> Hash (cost=76402.55..76402.55 rows=386544 width=4)
(actual time=121.170..121.170 rows=43898 loops=1)
-> Nested Loop (cost=368.81..76402.55 rows=386544
width=4) (actual time=8.645..104.842 rows=43898 loops=1)
-> Bitmap Heap Scan on plugins_guide_address
(cost=368.81..26374.83 rows=27673 width=4) (actual time=8.599..15.590
rows=26583 loops=1)
Recheck Cond: (city_id = 4535)
-> Bitmap Index Scan on
plugins_guide_address_city_id (cost=0.00..361.89 rows=27673 width=0)
(actual time=7.856..7.856 rows=26583 loops=1)
Index Cond: (city_id = 4535)
-> Index Scan using
plugins_plugin_addr_address_id on plugins_plugin_addr
(cost=0.00..1.63 rows=14 width=8) (actual time=0.002..0.003 rows=2
loops=26583)
Index Cond: (plugins_plugin_addr.address_id
= plugins_guide_address.id)
Total runtime: 1162.193 ms
(15 rows)

>(2) Temporarily take that top index out of consideration
It works nice! Query takes about 0.6 seconds as expected!

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 (cost=112274.36..112275.66 rows=5 width=4) (actual
time=200.758..637.039 rows=5 loops=1)
-> Merge Join (cost=112274.36..213042.22 rows=386544 width=4)
(actual time=200.754..637.035 rows=5 loops=1)
Merge Cond: (core_object.id = plugins_plugin_addr.oid_id)
-> Index Scan Backward using core_object_pkey on
core_object (cost=0.00..86916.44 rows=3221307 width=4) (actual
time=0.115..302.512 rows=1374693 loops=1)
-> Sort (cost=112274.36..113240.72 rows=386544 width=4)
(actual time=154.635..154.635 rows=5 loops=1)
Sort Key: plugins_plugin_addr.oid_id
Sort Method: quicksort Memory: 3594kB
-> Nested Loop (cost=368.81..76402.55 rows=386544
width=4) (actual time=9.522..126.206 rows=43898 loops=1)
-> Bitmap Heap Scan on plugins_guide_address
(cost=368.81..26374.83 rows=27673 width=4) (actual time=9.367..21.311
rows=26583 loops=1)
Recheck Cond: (city_id = 4535)
-> Bitmap Index Scan on
plugins_guide_address_city_id (cost=0.00..361.89 rows=27673 width=0)
(actual time=8.577..8.577 rows=26583 loops=1)
Index Cond: (city_id = 4535)
-> Index Scan using
plugins_plugin_addr_address_id on plugins_plugin_addr
(cost=0.00..1.63 rows=14 width=8) (actual time=0.002..0.003 rows=2
loops=26583)
Index Cond: (plugins_plugin_addr.address_id
= plugins_guide_address.id)
Total runtime: 637.620 ms
(15 rows)

> (3) Try it like this (untested, so you may need to fix it up):
explain analyze
SELECT core_object.id
from (SELECT id, city_id FROM "plugins_guide_address"
WHERE "city_id" = 4535) "plugins_guide_address"
JOIN "plugins_plugin_addr"
ON ("plugins_plugin_addr"."address_id"
= "plugins_guide_address"."id")
JOIN "core_object"
ON ("core_object"."id" = "plugins_plugin_addr"."oid_id")
ORDER BY "core_object"."id" DESC
LIMIT 5;
Limit (cost=0.00..11.51 rows=5 width=4) (actual
time=494.600..4737.867 rows=5 loops=1)
-> Merge Join (cost=0.00..889724.50 rows=386544 width=4) (actual
time=494.599..4737.862 rows=5 loops=1)
Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
-> Nested Loop (cost=0.00..789923.00 rows=386544 width=4)
(actual time=450.359..4269.608 rows=5 loops=1)
-> Index Scan Backward using
plugins_plugin_addr_oid_id on plugins_plugin_addr
(cost=0.00..45740.51 rows=1751340 width=8) (actual time=0.038..321.285
rows=1374690 loops=1)
-> Index Scan using plugins_guide_address_pkey on
plugins_guide_address (cost=0.00..0.41 rows=1 width=4) (actual
time=0.003..0.003 rows=0 loops=1374690)
Index Cond: (public.plugins_guide_address.id =
plugins_plugin_addr.address_id)
Filter: (public.plugins_guide_address.city_id =
4535)
-> Index Scan Backward using core_object_pkey on
core_object (cost=0.00..86916.44 rows=3221307 width=4) (actual
time=0.008..288.625 rows=1374693 loops=1)
Total runtime: 4737.964 ms
(10 rows)

So, as we can see, dropping index may help, but why? What shall I do
in my particular situation? Probably analyzing my tests help you
giving some recommendations, I hope so! :)

Thanks again for your time!

On Apr 14, 10:31 pm, Kevin(dot)Gritt(dot)(dot)(dot)(at)wicourts(dot)gov ("Kevin Grittner")
wrote:
> "Kevin Grittner" <Kevin(dot)Gritt(dot)(dot)(dot)(at)wicourts(dot)gov> wrote:
> > (3) Try it like this (untested, so you may need to fix it up):
>
> > explain analyze
> > SELECT core_object.id
> >   from (SELECT id, city_id FROM "plugins_guide_address")
> >        "plugins_guide_address"
> >   JOIN "plugins_plugin_addr"
> >     ON ("plugins_plugin_addr"."address_id"
> >        = "plugins_guide_address"."id")
> >   JOIN "core_object"
> >     ON ("core_object"."id" = "plugins_plugin_addr"."oid_id")
> >   WHERE "plugins_guide_address"."city_id" = 4535
> >   ORDER BY "core_object"."id" DESC
> >   LIMIT 4 -- or whatever it normally takes to cause the problem
> > ;
>
> Hmph.  I see I didn't take that quite where I intended.
> Forget the above and try this:
>
> explain analyze
> SELECT core_object.id
>   from (SELECT id, city_id FROM "plugins_guide_address"
>           WHERE "city_id" = 4535) "plugins_guide_address"
>   JOIN "plugins_plugin_addr"
>     ON ("plugins_plugin_addr"."address_id"
>        = "plugins_guide_address"."id")
>   JOIN "core_object"
>     ON ("core_object"."id" = "plugins_plugin_addr"."oid_id")
>   ORDER BY "core_object"."id" DESC
>   LIMIT 4 -- or whatever it normally takes to cause the problem
> ;
>
> -Kevin
>
> --
> Sent via pgsql-performance mailing list (pgsql-performa(dot)(dot)(dot)(at)postgresql(dot)org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance