pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: n0g0013 <ttw+pgsql@cobbled.net>
To: Mary Anderson <maryfran@demog.berkeley.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: am i creating a performance bottleneck?
Date: Wed, 18 Jul 2007 10:44:18 +0000
Message-ID: <20070718104418.GD16194@holyman.cobbled.net> (raw)
In-Reply-To: <469D5F4F.10307@demog.berkeley.edu>
References: <469D5F4F.10307@demog.berkeley.edu>

On 17.07-17:31, Mary Anderson wrote:
[ ... ]
> I am most worried about the second select, with its 'NOT EXISTS' 
> statement slowing everything down.  I would put appropriate indexes on 
> this -- namely an index on series for data and an index on dhd for 
> da_id.   My user community is a bunch of academics, so I am not under 
> the performance constraints I would have for a business application.
> 
> Would it help performance if I denormalized the database by attaching 
> series to the data_has_dimensions table?

to be honest i couldn't really understand your SQL but that probably
says more about me than you.  either way i don't think you need to
worry about the NOT EXISTS statement.  generally, serialising select
statements is better than joining and would suggest that if your series
data has 10,000 rows (assuming 10,00 was a typo) then i wouldn't merge
that data with another table (it also will simply mean a larger,
disjoint table, essentially equivelant to the join table required your
queries and thus may well cost you in overall performance).  i would
suggest you try to restructure you queries to serialise the 'series'
select joins (and preferably only doing it once), thereby reducing
the final join table to a minimum.

-- 
        t
 t
                 w



view thread (2+ messages)

Message-ID: <20070718104418.GD16194@holyman.cobbled.net>
Permalink:  ../20070718104418.GD16194@holyman.cobbled.net/
Also on:    postgresql.org/message-id/20070718104418.GD16194@holyman.cobbled.net

 · 

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-admin@postgresql.org
  Cc: ttw+pgsql@cobbled.net, maryfran@demog.berkeley.edu
  Subject: Re: am i creating a performance bottleneck?
  In-Reply-To: <20070718104418.GD16194@holyman.cobbled.net>

* 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