From: Robert Haas <robertmhaas@gmail.com>
To: norn <andrey.perliev@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: significant slow down with various LIMIT
Date: Fri, 9 Apr 2010 18:48:52 -0400
Message-ID: <u2l603c8f071004091548za8bba487w9f08ec6eeda47a6d@mail.gmail.com> (raw)
In-Reply-To: <9587baca-c902-4215-9863-7043802ec27e@10g2000yqq.googlegroups.com>
References: <9587baca-c902-4215-9863-7043802ec27e@10g2000yqq.googlegroups.com>
On Tue, Apr 6, 2010 at 8:42 PM, norn <andrey.perliev@gmail.com> wrote:
> I have some mysterious slow downs with ORDER BY and LIMIT. When LIMIT
> getting greater than some value (greater than 3 in my case), query
> takes 4-5 secs instead of 0.25ms. All of the necessary indexes are in
> place. I have no idea what to do, so any advices are welcome!
>
> Here my queries and explain analyzes;
>
> First Query with LIMIT 3 (fast)
> -------------
> 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 3;
>
> Limit (cost=0.00..9.57 rows=3 width=4) (actual time=0.090..0.138
> rows=3 loops=1)
> -> Merge Join (cost=0.00..1098182.56 rows=344125 width=4) (actual
> time=0.088..0.136 rows=3 loops=1)
> Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
> -> Nested Loop (cost=0.00..972804.02 rows=344125 width=4)
> (actual time=0.056..0.095 rows=3 loops=1)
> -> Index Scan Backward using
> plugins_plugin_addr_oid_id on plugins_plugin_addr
> (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..0.032
> rows=3 loops=1)
> -> Index Scan using plugins_guide_address_pkey on
> plugins_guide_address (cost=0.00..0.56 rows=1 width=4) (actual
> time=0.017..0.018 rows=1 loops=3)
> Index Cond: (plugins_guide_address.id =
> plugins_plugin_addr.address_id)
> Filter: (plugins_guide_address.city_id = 4535)
> -> Index Scan using core_object_pkey_desc on core_object
> (cost=0.00..113516.08 rows=3091134 width=4) (actual time=0.026..0.028
> rows=3 loops=1)
> Total runtime: 0.244 ms
> (10 rows)
>
> Second Query, the same, but with LIMIT 4 (slooooow)
> -------------
> 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 4;
>
> Limit (cost=0.00..12.76 rows=4 width=4) (actual time=0.091..4436.795
> rows=4 loops=1)
> -> Merge Join (cost=0.00..1098182.56 rows=344125 width=4) (actual
> time=0.089..4436.791 rows=4 loops=1)
> Merge Cond: (plugins_plugin_addr.oid_id = core_object.id)
> -> Nested Loop (cost=0.00..972804.02 rows=344125 width=4)
> (actual time=0.056..3988.249 rows=4 loops=1)
> -> Index Scan Backward using
> plugins_plugin_addr_oid_id on plugins_plugin_addr
> (cost=0.00..52043.06 rows=1621103 width=8) (actual time=0.027..329.942
> rows=1244476 loops=1)
> -> Index Scan using plugins_guide_address_pkey on
> plugins_guide_address (cost=0.00..0.56 rows=1 width=4) (actual
> time=0.003..0.003 rows=0 loops=1244476)
> Index Cond: (plugins_guide_address.id =
> plugins_plugin_addr.address_id)
> Filter: (plugins_guide_address.city_id = 4535)
> -> Index Scan using core_object_pkey_desc on core_object
> (cost=0.00..113516.08 rows=3091134 width=4) (actual
> time=0.027..284.195 rows=1244479 loops=1)
> Total runtime: 4436.894 ms
> (10 rows)
What do you get with no LIMIT at all?
...Robert
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: robertmhaas@gmail.com, andrey.perliev@gmail.com
Subject: Re: significant slow down with various LIMIT
In-Reply-To: <u2l603c8f071004091548za8bba487w9f08ec6eeda47a6d@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox