Обсуждение: Help needed with QueryPlan

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

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

Richard,
thanks for the reply. I actually did what you suggested but still the
same. Now i set ENABLE_SEQSCAN=false in the perl script which works but
I dont think thats the way it shold be done.

Alex

Richard Huxton wrote:

> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>