pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
are views typically any faster/slower than equivilent joins?
5+ messages / 3 participants
[nested] [flat]

* are views typically any faster/slower than equivilent joins?
@ 2003-06-01 02:55 Brian Tarbox <btarbox@theworld.com>
  2003-06-01 04:02 ` Re: are views typically any faster/slower than equivilent joins? Rod Taylor <rbt@rbt.ca>
  2003-06-01 04:05 ` Re: are views typically any faster/slower than equivilent joins? Rod Taylor <rbt@rbt.ca>
  0 siblings, 2 replies; 5+ messages in thread

From: Brian Tarbox @ 2003-06-01 02:55 UTC (permalink / raw)
  To: pgsql-performance

I am working with a highly normalized database.  Most of my meaningful
queries involve joins from a primary table to 4-6 secondary tables.

Would my query performance be significantly faster/slower using a View as
opposed to a prepared query using join?

(Assume all join fields are ints, say 10,000 records in main table and a few
dozen records in each of the secondary tables).

Thank you.

Brian Tarbox




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: are views typically any faster/slower than equivilent joins?
  2003-06-01 02:55 are views typically any faster/slower than equivilent joins? Brian Tarbox <btarbox@theworld.com>
@ 2003-06-01 04:02 ` Rod Taylor <rbt@rbt.ca>
  2003-06-01 05:43   ` Re: are views typically any faster/slower than equivilent joins? Bruno Wolff III <bruno@wolff.to>
  1 sibling, 1 reply; 5+ messages in thread

From: Rod Taylor @ 2003-06-01 04:02 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; +Cc: pgsql-performance

On Sat, 2003-05-31 at 22:55, Brian Tarbox wrote:
> I am working with a highly normalized database.  Most of my meaningful
> queries involve joins from a primary table to 4-6 secondary tables.
> 
> Would my query performance be significantly faster/slower using a View as
> opposed to a prepared query using join?

There are some corner cases where a view would be slower than a standard
query in 7.3 (bug fix / disabled optimization -- fixed right in 7.4),
but generally you can assume it will be about the same speed.


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.

-- 
Rod Taylor <rbt@rbt.ca>

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

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: are views typically any faster/slower than equivilent joins?
  2003-06-01 02:55 are views typically any faster/slower than equivilent joins? Brian Tarbox <btarbox@theworld.com>
  2003-06-01 04:02 ` Re: are views typically any faster/slower than equivilent joins? Rod Taylor <rbt@rbt.ca>
@ 2003-06-01 05:43   ` Bruno Wolff III <bruno@wolff.to>
  2003-06-01 11:56     ` Re: are views typically any faster/slower than Rod Taylor <rbt@rbt.ca>
  0 siblings, 1 reply; 5+ messages in thread

From: Bruno Wolff III @ 2003-06-01 05:43 UTC (permalink / raw)
  To: Rod Taylor <rbt@rbt.ca>; +Cc: Brian Tarbox <btarbox@theworld.com>; pgsql-performance

On Sun, Jun 01, 2003 at 00:02:39 -0400,
  Rod Taylor <rbt@rbt.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.



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: are views typically any faster/slower than
  2003-06-01 02:55 are views typically any faster/slower than equivilent joins? Brian Tarbox <btarbox@theworld.com>
  2003-06-01 04:02 ` Re: are views typically any faster/slower than equivilent joins? Rod Taylor <rbt@rbt.ca>
  2003-06-01 05:43   ` Re: are views typically any faster/slower than equivilent joins? Bruno Wolff III <bruno@wolff.to>
@ 2003-06-01 11:56     ` Rod Taylor <rbt@rbt.ca>
  0 siblings, 0 replies; 5+ messages in thread

From: Rod Taylor @ 2003-06-01 11:56 UTC (permalink / raw)
  To: Bruno Wolff III <bruno@wolff.to>; +Cc: Brian Tarbox <btarbox@theworld.com>; 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@rbt.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@rbt.ca>

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

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: are views typically any faster/slower than equivilent joins?
  2003-06-01 02:55 are views typically any faster/slower than equivilent joins? Brian Tarbox <btarbox@theworld.com>
@ 2003-06-01 04:05 ` Rod Taylor <rbt@rbt.ca>
  1 sibling, 0 replies; 5+ messages in thread

From: Rod Taylor @ 2003-06-01 04:05 UTC (permalink / raw)
  To: Brian Tarbox <btarbox@theworld.com>; +Cc: pgsql-performance

> Would my query performance be significantly faster/slower using a View as
> opposed to a prepared query using join?

I missed this part.  Views and prepared queries are not the same time. 
Use of a view still needs to be optimized.

Prepared queries will run the optimization portion on the entire query
including the view segments of it.  Think of a view as a MACRO. 
Depending on the context of what surrounds it, the view may be executed
very differently.

-- 
Rod Taylor <rbt@rbt.ca>

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

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2003-06-01 11:56 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-06-01 02:55 are views typically any faster/slower than equivilent joins? Brian Tarbox <btarbox@theworld.com>
2003-06-01 04:02 ` Rod Taylor <rbt@rbt.ca>
2003-06-01 05:43   ` Bruno Wolff III <bruno@wolff.to>
2003-06-01 11:56     ` Re: are views typically any faster/slower than Rod Taylor <rbt@rbt.ca>
2003-06-01 04:05 ` Rod Taylor <rbt@rbt.ca>

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