Hi all,
I've got a simple table with a lot of data in it:
CREATE TABLE customer_data (
cd_id int primary key
default(nextval('cd_seq')),
cd_cust_id int not null,
cd_variable textnot null,
cd_valuetext,
cd_tag text,
added_user int not null,
added_date timestamp not nulldefault now(),
modified_user int not null,
modified_date timestamp not nulldefault now(),
FOREIGN KEY(cd_cust_id) REFERENCES customer(cust_id)
);
The 'cust_id' references the customer that the given data belongs to.
The reason for this data bucket (does this structure have a proper
name?) is that the data I need to store on a give customer is quite
variable and outside of my control. As it is, there is about 400
different variable/value pairs I need to store per customer.
This table has a copy in a second historical schema that matches this
one in public but with an additional 'history_id' sequence. I use a
simple function to copy an INSERT or UPDATE to any entry in the
historical schema.
Now I want to graph a certain subset of these variable/value pairs,
so I created a simple (in concept) view to pull out the historical data
set for a given customer. I do this by pulling up a set of records based
on the name of the 'cd_variable' and 'cd_tag' and connect the records
together using a matching timestamp.
The problem is that this view has very quickly become terribly slow.
I've got indexes on the 'cd_variable', 'cd_tag' and the parent
'cust_id' columns, and the plan seems to show that the indexes are
indeed being used, but the query against this view can take up to 10
minutes to respond. I am hoping to avoid making a dedicated table as
what I use to build this dataset may change over time.
Below I will post the VIEW and a sample of the query's EXPLAIN
ANALYZE. Thanks for any tips/help/clue-stick-beating you may be able to
share!
Madi
-=] VIEW
CREATE VIEW view_sync_rate_history AS
SELECT
a.cust_id ASvsrh_cust_id,
a.cust_name ASvsrh_cust_name,
a.cust_business ASvsrh_cust_business,
a.cust_nexxia_id||'-'||a.cust_nexxia_seqASvsrh_cust_nexxia,
a.cust_phoneASvsrh_cust_phone,
b.cd_value ASvsrh_up_speed,
b.history_idASvsrh_up_speed_history_id,
c.cd_value ASvsrh_up_rco,
c.history_idASvsrh_up_rco_history_id,
d.cd_value ASvsrh_up_nm,
d.history_idASvsrh_up_nm_history_id,
e.cd_value ASvsrh_up_sp,
e.history_idASvsrh_up_sp_history_id,
f.cd_value ASvsrh_up_atten,
f.history_idASvsrh_up_atten_history_id,
g.cd_value ASvsrh_down_speed,
g.history_idASvsrh_down_speed_history_id,
h.cd_value ASvsrh_down_rco,
h.history_idASvsrh_down_rco_history_id,
i.cd_value ASvsrh_down_nm,
i.history_idASvsrh_down_nm_history_id,
j.cd_value ASvsrh_down_sp,
j.history_idASvsrh_down_sp_history_id,
k.cd_value ASvsrh_down_atten,
k.history_idASvsrh_down_atten_history_id,
l.cd_value ASvsrh_updated,
l.history_idASvsrh_updated_history_id
FROM
customer a,
history.customer_data b,
history.customer_data c,
history.customer_data d,
history.customer_data e,
history.customer_data f,
history.customer_data g,
history.customer_data h,
history.customer_data i,
history.customer_data j,
history.customer_data k,
history.customer_data l
WHERE
a.cust_id=b.cd_cust_id AND
a.cust_id=c.cd_cust_id AND
a.cust_id=d.cd_cust_id AND
a.cust_id=e.cd_cust_id AND
a.cust_id=f.cd_cust_id AND
a.cust_id=g.cd_cust_id AND
a.cust_id=h.cd_cust_id AND
a.cust_id=i.cd_cust_id AND
a.cust_id=j.cd_cust_id AND
a.cust_id=k.cd_cust_id AND
a.cust_id=l.cd_cust_id AND
b.cd_tag='sync_rate' AND
c.cd_tag='sync_rate' AND
d.cd_tag='sync_rate' AND
e.cd_tag='sync_rate' AND
f.cd_tag='sync_rate' AND
g.cd_tag='sync_rate' AND
h.cd_tag='sync_rate' AND
i.cd_tag='sync_rate' AND
j.cd_tag='sync_rate' AND
k.cd_tag='sync_rate' AND
l.cd_tag='sync_rate' AND
b.cd_variable='upstream_speed' AND
c.cd_variable='upstream_relative_capacity_occupation' AND
d.cd_variable='upstream_noise_margin' AND
e.cd_variable='upstream_signal_power' AND
f.cd_variable='upstream_attenuation'
