From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Alex <alex(at)meerkatsoft(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Help needed with QueryPlan |
Date: | 2004-12-14 09:34:12 |
Message-ID: | 41BEB394.4030708@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Alex wrote:
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Hengki Suhartoyo | 2004-12-14 10:46:44 | increasing max_connections on freebsd |
Previous Message | Együd Csaba | 2004-12-14 08:49:59 | Insufficient memory for this operation. |