From: Richard Huxton <dev@archonet.com>
To: Dustin Sallings <dustin@spy.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: inconsistent/weird index usage
Date: Fri, 01 Oct 2004 15:43:05 +0100
Message-ID: <415D6CF9.60909@archonet.com> (raw)
In-Reply-To: <6FA9AB99-1373-11D9-A87D-000A957659CC@spy.net>
References: <6FA9AB99-1373-11D9-A87D-000A957659CC@spy.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, dustin@spy.net
Subject: Re: inconsistent/weird index usage
In-Reply-To: <415D6CF9.60909@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox