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 AD696329E5D for ; Fri, 1 Oct 2004 15:43:18 +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 20067-04 for ; Fri, 1 Oct 2004 14:43:10 +0000 (GMT) Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by svr1.postgresql.org (Postfix) with ESMTP id C2D8D329C6B for ; Fri, 1 Oct 2004 15:43:12 +0100 (BST) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-32.mail.demon.net with esmtp (Exim 4.42) id 1CDOck-0000a5-7W; Fri, 01 Oct 2004 14:43:06 +0000 Received: from [192.168.1.17] (client17.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id A9E4F17988; Fri, 1 Oct 2004 15:43:05 +0100 (BST) Message-ID: <415D6CF9.60909@archonet.com> Date: Fri, 01 Oct 2004 15:43:05 +0100 From: Richard Huxton User-Agent: Mozilla Thunderbird 0.7 (X11/20040615) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Dustin Sallings Cc: pgsql-performance@postgresql.org Subject: Re: inconsistent/weird index usage References: <6FA9AB99-1373-11D9-A87D-000A957659CC@spy.net> In-Reply-To: <6FA9AB99-1373-11D9-A87D-000A957659CC@spy.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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/5 X-Sequence-Number: 8481 Dustin Sallings wrote: > 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 Try this with UNION ALL (you know there won't be any duplicates) and possibly with some limits too: SELECT * FROM samples_1999 WHERE ts BETWEEN '1999-01-01 00:00:00+00' AND '1999-12-31 11:59:59+00' UNION ALL ... > select > s.serial as serial_num, > s.name as name, > date(ts) as day, > min(sample) as min_temp, > avg(sample) as avg_temp, > stddev(sample) as stddev_temp, > max(sample) as max_temp > from > samples inner join sensors s using (sensor_id) > where > ts > current_date - 7 > group by > serial_num, name, day > order by > serial_num, day desc Try restricting the timestamp too WHERE ts BETWEEN (current_date -7) AND current_timestamp Hopefully that will give the planner enough smarts to know it can skip most of the sample_200x tables. -- Richard Huxton Archonet Ltd