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.Gritt...@wicourts.gov ("Kevin Grittner")
wrote:
> "Kevin Grittner" <Kevin.Gritt...@wicourts.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...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-performance