pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (8+ messages)  latest in thread

Message-ID: <415D6CF9.60909@archonet.com>
Permalink:  ../415D6CF9.60909@archonet.com/
Also on:    postgresql.org/message-id/415D6CF9.60909@archonet.com

 · 

reply

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