X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 587C53A62C8 for ; Mon, 13 Dec 2004 22:08:30 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00707-04 for ; Mon, 13 Dec 2004 22:08:18 +0000 (GMT) Received: from smtp-gw-cl-d.dmv.com (smtp-gw-cl-d.dmv.com [216.240.97.42]) by svr1.postgresql.org (Postfix) with ESMTP id D3CE23A6655 for ; Mon, 13 Dec 2004 22:08:18 +0000 (GMT) Received: from mail-gw-cl-b.dmv.com (mail-gw-cl-b.dmv.com [216.240.97.39]) by smtp-gw-cl-d.dmv.com (8.12.10/8.12.10) with ESMTP id iBDMFUWa080491; Mon, 13 Dec 2004 17:15:31 -0500 (EST) (envelope-from sven@dmv.com) Received: from [64.45.134.154] (dogpound.dyndns.org [64.45.134.154]) by mail-gw-cl-b.dmv.com (8.12.9/8.12.9) with ESMTP id iBDM87eE055424; Mon, 13 Dec 2004 17:08:08 -0500 (EST) (envelope-from sven@dmv.com) Message-ID: <41BE1270.7060908@dmv.com> Date: Mon, 13 Dec 2004 17:06:40 -0500 From: Sven Willenberger User-Agent: Mozilla Thunderbird 0.9 (Windows/20041103) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Cc: andrew@catalyst.net.nz Subject: Re: Using LIMIT changes index used by planner References: <41BD3317.9090507@dmv.com> <1102931789.11712.9.camel@lamb.mcmillan.net.nz> In-Reply-To: <1102931789.11712.9.camel@lamb.mcmillan.net.nz> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Scanned-By: MIMEDefang 2.48 on 216.240.97.42 X-Scanned-By: MIMEDefang 2.48 on 216.240.97.39 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200412/171 X-Sequence-Number: 9546 Andrew McMillan wrote: > On Mon, 2004-12-13 at 01:13 -0500, Sven Willenberger wrote: > >>I have a question regarding a serious performance hit taken when using a >>LIMIT clause. I am using version 7.4.6 on FreeBSD 4.10-STABLE with 2GB >>of memory. The table in question contains some 25 million rows with a >>bigserial primary key, orderdate index and a referrer index. The 2 >>select statements are as follow: > > > It's an interesting question, but to be able to get answers from this > list you will need to provide "EXPLAIN ANALYZE ..." rather than just > "EXPLAIN ...". > A) Query without limit clause: explain analyze select storelocation,order_number from custacct where referrer = 1365 and orderdate between '2004-12-07' and '2004-12-07 12:00:00' order by custacctid; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=1226485.32..1226538.78 rows=21382 width=43) (actual time=30340.322..30426.274 rows=21432 loops=1) Sort Key: custacctid -> Index Scan using orderdate_idx on custacct (cost=0.00..1224947.52 rows=21382 width=43) (actual time=159.218..30196.686 rows=21432 loops=1) Index Cond: ((orderdate >= '2004-12-07 00:00:00'::timestamp without time zone) AND (orderdate <= '2004-12-07 12:00:00'::timestamp without time zone)) Filter: (referrer = 1365) Total runtime: 30529.151 ms (6 rows) ************************************ A2) Same query run again, to see effect of caching: explain analyze select storelocation,order_number from custacct where referrer = 1365 and orderdate between '2004-12-07' and '2004-12-07 12:00:00' order by custacctid; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=1226485.32..1226538.78 rows=21382 width=43) (actual time=1402.410..1488.395 rows=21432 loops=1) Sort Key: custacctid -> Index Scan using orderdate_idx on custacct (cost=0.00..1224947.52 rows=21382 width=43) (actual time=0.736..1259.964 rows=21432 loops=1) Index Cond: ((orderdate >= '2004-12-07 00:00:00'::timestamp without time zone) AND (orderdate <= '2004-12-07 12:00:00'::timestamp without time zone)) Filter: (referrer = 1365) Total runtime: 1590.675 ms (6 rows) *********************************** B) Query run with LIMIT explain analyze select storelocation,order_number from custacct where referrer = 1365 and orderdate between '2004-12-07' and '2004-12-07 12:00:00' order by custacctid limit 10; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..43065.76 rows=10 width=43) (actual time=1306957.216..1307072.111 rows=10 loops=1) -> Index Scan using custacct2_pkey on custacct (cost=0.00..92083209.38 rows=21382 width=43) (actual time=1306957.205..1307072.017 rows=10 loops=1) Filter: ((referrer = 1365) AND (orderdate >= '2004-12-07 00:00:00'::timestamp without time zone) AND (orderdate <= '2004-12-07 12:00:00'::timestamp without time zone)) Total runtime: 1307072.231 ms (4 rows) ************************************ C) Query using the subselect variation explain analyze select foo.storelocation, foo.order_number from (select storelocation,order_number from custacct where referrer = 1365 and orderdate between '2004-12-07' and '2004-12-07 12:00:00' order by custacctid) as foo limit 10; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1226485.32..1226485.45 rows=10 width=100) (actual time=1413.829..1414.024 rows=10 loops=1) -> Subquery Scan foo (cost=1226485.32..1226752.60 rows=21382 width=100) (actual time=1413.818..1413.933 rows=10 loops=1) -> Sort (cost=1226485.32..1226538.78 rows=21382 width=43) (actual time=1413.798..1413.834 rows=10 loops=1) Sort Key: custacctid -> Index Scan using orderdate_idx on custacct (cost=0.00..1224947.52 rows=21382 width=43) (actual time=0.740..1272.380 rows=21432 loops=1) Index Cond: ((orderdate >= '2004-12-07 00:00:00'::timestamp without time zone) AND (orderdate <= '2004-12-07 12:00:00'::timestamp without time zone)) Filter: (referrer = 1365) Total runtime: 1418.964 ms (8 rows) Thanks, Sven