[ADMIN] am i creating a performance bottleneck?

[Mary Anderson]

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend