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 811B732A26F for ; Fri, 1 Oct 2004 15:38:51 +0100 (BST) 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 15030-08 for ; Fri, 1 Oct 2004 14:38:43 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 2314832A234 for ; Fri, 1 Oct 2004 15:38:46 +0100 (BST) 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 i91Eck99009346; Fri, 1 Oct 2004 10:38:46 -0400 (EDT) To: Dustin Sallings Cc: pgsql-performance@postgresql.org Subject: Re: inconsistent/weird index usage In-reply-to: <6FA9AB99-1373-11D9-A87D-000A957659CC@spy.net> References: <6FA9AB99-1373-11D9-A87D-000A957659CC@spy.net> Comments: In-reply-to Dustin Sallings message dated "Thu, 30 Sep 2004 23:30:49 -0700" Date: Fri, 01 Oct 2004 10:38:46 -0400 Message-ID: <9345.1096641526@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/4 X-Sequence-Number: 8480 Dustin Sallings writes: > The following view creates the illusion of the old ``single-table'' > model: > create view samples as > select * from samples_1999 > union select * from samples_2000 > union select * from samples_2001 > union select * from samples_2002 > union select * from samples_2003 > union select * from samples_2004 You really, really, really want to use UNION ALL not UNION here. > OK, now that that's over with, I have this one particular query that I > attempt to run for a report from my phone that no longer works because > it tries to do a table scan on *some* of the tables. Why it chooses > this table scan, I can't imagine. Most of the problem here comes from the fact that "current_date - 7" isn't reducible to a constant and so the planner is making bad guesses about how much of each table will be scanned. If possible, do the date arithmetic on the client side and send over a simple literal constant. If that's not practical you can fake it with a mislabeled IMMUTABLE function --- see the list archives for previous discussions of the same issue. regards, tom lane