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 567093A6888 for ; Mon, 13 Dec 2004 06:13:55 +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 82798-02 for ; Mon, 13 Dec 2004 06:13:44 +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 A77A73A678A for ; Mon, 13 Dec 2004 06:13:43 +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 iBD6KwWa041846 for ; Mon, 13 Dec 2004 01:21:00 -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 iBD6DeeE014781 for ; Mon, 13 Dec 2004 01:13:40 -0500 (EST) (envelope-from sven@dmv.com) Message-ID: <41BD3317.9090507@dmv.com> Date: Mon, 13 Dec 2004 01:13:43 -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 Subject: Using LIMIT changes index used by planner 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/155 X-Sequence-Number: 9530 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: A) 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; B) 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; So the only difference is the use of the Limit, which, in theory, should be quicker after custacctid is ordered. Now the analyze results: A) explain 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=904420.55..904468.11 rows=19025 width=44) Sort Key: custacctid -> Index Scan using orderdate_idx on custacct (cost=0.00..903068.29 rows=19025 width=44) 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) (5 rows) ************************ B) explain 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..33796.50 rows=10 width=44) -> Index Scan using custacct2_pkey on custacct (cost=0.00..64297840.86 rows=19025 width=44) 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)) (3 rows) ******************* Notice the huge cost difference in the two plans: 904468 in the one without LIMIT versus 64297840.86 for the index scan on custacct index. Why would the planner switch from using the orderdate index to the custacct index (which is a BIGSERIAL, btw)? I can change that behavior (and speed up the resultant query) by using the following subquery: explain 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=904420.55..904420.67 rows=10 width=100) -> Subquery Scan foo (cost=904420.55..904658.36 rows=19025 width=100) -> Sort (cost=904420.55..904468.11 rows=19025 width=44) Sort Key: custacctid -> Index Scan using orderdate_idx on custacct (cost=0.00..903068.29 rows=19025 width=44) 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) (7 rows) As a side note, when running query A, the query takes 1772.523 ms, when running the subselect version to get the limit, it takes 1415.615 ms. Running option B (with the other index being scanned) takes several minutes (close to 10 minutes!). What am I missing about how the planner views the LIMIT statement? Sven