From: | Alex <alex(at)meerkatsoft(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Help needed with QueryPlan |
Date: | 2004-12-14 02:37:40 |
Message-ID: | 41BE51F4.6030809@meerkatsoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I have a query that runs pretty slow and tried to use explain to see
where the problem is.
Both tables have 2.1 and 2.8 million records.
In the Explain output I see that a Seq Scan is used on nval_price_hist
what I dont quite understand.
Could some one help me creating the correct indices?
SELECT ..........
FROM price_hist AS P, nval_price_hist AS N
WHERE P.price_id = N.price_id AND P.sec_code = 512084 AND P.eval_date =
'2004-10-15' ;
Hash Join (cost=210.16..61370.19 rows=53 width=602)
Hash Cond: ("outer".price_id = "inner".price_id)
-> Seq Scan on nval_price_hist n (cost=0.00..50575.00 rows=2116900
width=65)
-> Hash (cost=209.99..209.99 rows=69 width=537)
-> Index Scan using price_hist_idx1 on price_hist p
(cost=0.00..209.99 rows=69 width=537)
Index Cond: ((eval_date = '2004-10-15'::date) AND
(sec_code = 512084))
Indices on nval_price_hist
"nval_price_hist_pkey" primary key, btree (price_id, logic_id)
"nval_price_hist_idx1" btree (sec_code, exch_code, sec_type,
nval_price_date, logic_id)
"nval_price_hist_idx2" btree (nval_price_date, logic_id)
"nval_price_hist_idx3" btree (price_id)
"nval_price_hist_idx4" btree (sec_code, nval_price_date)
Thanks
Alex
From | Date | Subject | |
---|---|---|---|
Next Message | Bruno Wolff III | 2004-12-14 05:32:46 | Re: Multiple foreign keys on same field |
Previous Message | Iain | 2004-12-14 01:54:17 | Re: pg_restore taking 4 hours! |