pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mary Anderson <maryfran@demog.berkeley.edu>
To: pgsql-admin@postgresql.org
Subject: am i creating a performance bottleneck?
Date: Tue, 17 Jul 2007 17:31:11 -0700
Message-ID: <469D5F4F.10307@demog.berkeley.edu> (raw)

Hi all,

    In order to save a fair amount of space I am creating a schema 
against which I will want to run the following simplified query.  Think 
of the size of the tables as data ~ 30 M rows,  series has 10,00 
entries, data_has_dimensions has 300 K rows, dimensions is tiny

select   population = da.value,
          residence = di.value
from data da, data_has_dimension dhd, dimensions di, series si
where da.series_id = si.series_id
and si.series = 'my-series'
and dhd.data_id = da.data_id
and dhd.dimension_id = di.dimension_id
and di.dimension = 'residence'

UNION

select population = da.value,
        residence = 'total'
where da.series_id = si.series_id
   and  si.series = 'my_series'
   and NOT EXISTS(select dhd.da_id
                  from data_has_dimensions dhd, dimensions di
                  where di.dimension = 'residence'
                  and dhd.dimension_id = di.dimension_id
                  and dhd.da_id = da.da_id)

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?

Thanks,
Mary Anderson



view thread (2+ messages)  latest in thread

Message-ID: <469D5F4F.10307@demog.berkeley.edu>
Permalink:  ../469D5F4F.10307@demog.berkeley.edu/
Also on:    postgresql.org/message-id/469D5F4F.10307@demog.berkeley.edu

 · 

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: maryfran@demog.berkeley.edu
  Subject: Re: am i creating a performance bottleneck?
  In-Reply-To: <469D5F4F.10307@demog.berkeley.edu>

* 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