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 6F66440C0BA for ; Sun, 30 Jan 2005 17:51:35 +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 53646-06 for ; Sun, 30 Jan 2005 17:51:31 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 5DF3840C147 for ; Sun, 30 Jan 2005 17:50:10 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j0UHo3nU003658; Sun, 30 Jan 2005 12:50:03 -0500 (EST) To: PFC Cc: "Ken Egervari" , pgsql-performance@postgresql.org Subject: Re: Performance problem with semi-large tables In-reply-to: References: <001601c50416$57090e10$cd422418@a96dfxb4kjzogw> <002201c50654$1a8c50b0$cd422418@a96dfxb4kjzogw> <000a01c5066a$0d1f43e0$cd422418@a96dfxb4kjzogw> Comments: In-reply-to PFC message dated "Sun, 30 Jan 2005 12:10:55 +0100" Date: Sun, 30 Jan 2005 12:50:02 -0500 Message-ID: <3657.1107107402@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200501/621 X-Sequence-Number: 10311 PFC writes: >> For example, let's add another filter to get all the shipments with >> release code '5' that are 7 days old or newer. >> >> ss.date >= current_date - 7 > It's the order by + limit which makes the query behaves badly, and which > forces use of kludges to use the index. If you add another condition like > that, it should be a breeze. Actually, that date condition has its own problem, namely that the compared-to value isn't a constant. The 8.0 planner is able to realize that this is a pretty selective condition, but prior releases fall back on a very pessimistic default estimate. I'm sure that has something to do with Ken not being able to get it to use an index on date. regards, tom lane