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?
Hmm - it knows there are 2 million rows (2116900) in nval_price_hist and yet it isn't using the index on price_id even though it's expecting a small number of rows (53) to come out of the join.
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
...
1. VACUUM FULL ANALYSE the two tables. 2. Re-run the query with EXPLAIN ANALYSE instead of just EXPLAIN 3. SET ENABLE_SEQSCAN=false; then re-run step 2
That will ensure the statistics are up-to-date, show the actual costs as well as the expected costs and finally let us compare the index against a sequential scan.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
