Re: are views typically any faster/slower than

From: Rod Taylor <rbt(at)rbt(dot)ca>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: Brian Tarbox <btarbox(at)theworld(dot)com>, Postgresql Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: are views typically any faster/slower than
Date: 2003-06-01 11:56:15
Message-ID: 1054468575.11968.95.camel@jester
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Sun, 2003-06-01 at 01:43, Bruno Wolff III wrote:
> On Sun, Jun 01, 2003 at 00:02:39 -0400,
> Rod Taylor <rbt(at)rbt(dot)ca> wrote:
> >
> > Some views such as unions will not be as fast as you would like, but
> > thats a general issue with PostgreSQLs inability to throw away selects
> > when it won't find results on one side of a union.
> >
> > CREATE VIEW sales AS SELECT * FROM sales_archive_2002 UNION ALL SELECT *
> > FROM sales_current;
> >
> >
> > SELECT * FROM sales WHERE timestamp => CURRENT_TIMESTAMP - INTERVAL '1
> > day';
> >
> > The above query would not be so quick.
>
> I thought some work had been done on pushing where conditions down into
> unions? If so the above wouldn't be too bad. It would still look at
> the archive table, but it should return no rows relatively quickly
> assuming an appropiate index exists.

Certainly, if the index exists it won't be so bad (for any single
archive table). It's when the index doesn't exist or there are several
hundred archive tables then it starts to get a little worse.

Anyway, anyone doing the above in PostgreSQL should probably be looking
at partial indexes and merging the information back into a single table
again.

--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Rod Taylor 2003-06-01 12:14:15 Re: Index speeds up one row table (why)?
Previous Message Dave E Martin XXIII 2003-06-01 07:20:03 Re: Index speeds up one row table (why)?