Re: significant slow down with various LIMIT

From: Kevin Grittner
Subject: Re: significant slow down with various LIMIT
Date: ,
Msg-id: 4BCDDCE70200002500030C1E@gw.wicourts.gov
(view: Whole thread, Raw)
In response to: Re: significant slow down with various LIMIT  (norn)
Responses: Re: significant slow down with various LIMIT  ("Kevin Grittner")
List: pgsql-performance

Tree view

significant slow down with various LIMIT  (Helio Campos Mello de Andrade, )
 Re: significant slow down with various LIMIT  ("Kevin Grittner", )
  Re: significant slow down with various LIMIT  (norn, )
   Re: significant slow down with various LIMIT  ("Kevin Grittner", )
  Re: significant slow down with various LIMIT  (norn, )
   Re: significant slow down with various LIMIT  ("Kevin Grittner", )
    Re: significant slow down with various LIMIT  (Chris Bowlby, )
    Re: significant slow down with various LIMIT  ("Kevin Grittner", )
  Re: significant slow down with various LIMIT  (norn, )
   Re: significant slow down with various LIMIT  ("Kevin Grittner", )
    Re: significant slow down with various LIMIT  ("Kevin Grittner", )
  Re: significant slow down with various LIMIT  (norn, )
  Re: significant slow down with various LIMIT  (norn, )

norn <> 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


pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: performance change from 8.3.1 to later releases
From: Greg Smith
Date:
Subject: Re: Very high effective_cache_size == worse performance?