am i creating a performance bottleneck?
am 18.07.2007 02:31:11 von Mary AndersonHi 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