Re: significant slow down with various LIMIT

norn <andrey.perliev@gmail.com> wrote:

>> (1) Try it without the ORDER BY clause and the LIMIT.
> W/o the 'order by' it works instantly (about 1ms!)

> W/o the limit it takes 1.4 seconds

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

> 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! :)

The combination of the ORDER BY DESC and the LIMIT causes it to
think it can get the right data most quickly by scanning backwards
on the index.  It's wrong about that.  With the information from the
additional plans, it seems that this bad estimate might be why it's
not recognizing the plan which is actually four orders of magnitude
faster:

Index Scan using plugins_guide_address_city_id
  on plugins_guide_address
  Index Cond: (city_id = 4535)
  estimated rows=27673
  actual rows=5

Try this:

ALTER TABLE ALTER plugins_guide_address
  ALTER COLUMN city_id SET STATISTICS 1000;
ANALYZE plugins_guide_address;

Then try your query.

I have one more diagnostic query to test, if the above doesn't work:

explain analyze
SELECT id FROM
  (
    SELECT core_object.id
      FROM "core_object"
      JOIN "plugins_plugin_addr"
        ON ("core_object"."id" = "plugins_plugin_addr"."oid_id")
      JOIN "plugins_guide_address"
        ON ("plugins_plugin_addr"."address_id" =
            "plugins_guide_address"."id")
      WHERE "plugins_guide_address"."city_id" = 4535
  ) x
  ORDER BY id DESC
  LIMIT 4;

-Kevin