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
Index Scan using plugins_guide_address_city_id
Index Cond: (city_id = 4535)
ALTER TABLE ALTER plugins_guide_address
ALTER COLUMN city_id SET STATISTICS 1000;
Then try your query.
I have one more diagnostic query to test, if the above doesn't work:
SELECT id FROM
ON ("core_object"."id" = "plugins_plugin_addr"."oid_id")
ON ("plugins_plugin_addr"."address_id" =
WHERE "plugins_guide_address"."city_id" = 4535
ORDER BY id DESC
In response to
pgsql-performance by date
|Next:||From: Tom Lane||Date: 2010-04-21 03:47:01|
|Subject: Re: performance change from 8.3.1 to later releases |
|Previous:||From: Kris Jurka||Date: 2010-04-20 21:05:54|
|Subject: 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