pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: KeithW@narrowpathinc.com
Cc: PostgreSQL Perform <pgsql-performance@postgresql.org>
Subject: Re: 4 way JOIN using aliases
Date: Sun, 10 Apr 2005 18:41:05 -0700
Message-ID: <200504101841.05234.josh@agliodbs.com> (raw)
In-Reply-To: <20050407140133.M89867@narrowpathinc.com>
References: <20050407140133.M89867@narrowpathinc.com>

Keith,

> Thanks to all on the NOVICE list that gave me help I now have a query
> running that returns the results I am after. :-)  Now of course I want it
> to run faster.  Currently it clocks in at ~160ms.  I have checked over the
> indexes and I belive that the tables are indexed properly.  The largest
> table, tbl_item, only has 2000 rows.  Is it possible to reduce the time of
> this query further?  

Probably not, no.    For a 7-way join including 2 LEFT JOINs on the 
unrestricted contents of all tables, 160ms is pretty darned good.   If these 
tables were large, you'd be looking at a much longer estimation time.   The 
only real way to speed it up would be to find a way to eliminate the left 
joins.  Also, PostgreSQL 8.0 might optimize this query a little better.

The only thing I can see to tweak is that the estimate on the number of rows 
in tbl_item is wrong; probably you need to ANALYZE tbl_item.   But I doubt 
that will make a difference in execution time.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



view thread (7+ messages)  latest in thread

Message-ID: <200504101841.05234.josh@agliodbs.com>
Permalink:  ../200504101841.05234.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200504101841.05234.josh@agliodbs.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: josh@agliodbs.com, KeithW@narrowpathinc.com
  Subject: Re: 4 way JOIN using aliases
  In-Reply-To: <200504101841.05234.josh@agliodbs.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