am i creating a performance bottleneck? - Mailing list pgsql-admin

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