Re: significant slow down with various LIMIT

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "norn" <andrey(dot)perliev(at)gmail(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: significant slow down with various LIMIT
Date: 2010-04-20 21:57:11
Message-ID: 4BCDDCE70200002500030C1E@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-performance

norn <andrey(dot)perliev(at)gmail(dot)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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2010-04-21 03:47:01 Re: performance change from 8.3.1 to later releases
Previous Message Kris Jurka 2010-04-20 21:05:54 Re: SOLVED ... Re: Getting rid of a cursor from JDBC .... Re: [PERFORM] Re: HELP: How to tame the 8.3.x JDBC driver with a biq guery result set