Re: Planner ignoring to use INDEX SCAN

Ashish Karalkar wrote:
>
> Richard Huxton <dev@archonet.com> wrote: Ashish Karalkar wrote:
>> query which was taking seconds on the join of these two table
>> suddenly started taking 20/25 min
>
> Show the EXPLAIN ANALYSE of your problem query and someone will be able
> to tell you why.
>
> Here is the output from explain analyse:

Actually, this is the output from EXPLAIN not EXPLAIN ANALYSE. It
doesn't show what actually happened, just what the planner thought was
going to happen.

Are the row-estimates roughly accurate?

> table structures are more or less same with delivery being parent and sms_new being child having index on deliveryid
inboth tables. 
>
>  HashAggregate  (cost=6153350.21..6153352.38 rows=174 width=32)
>    ->  Hash Join  (cost=218058.30..6153259.97 rows=6016 width=32)
>          Hash Cond: ("outer".deliveryid = "inner".deliveryid)
>          ->  Seq Scan on sms_new  (cost=0.00..5240444.80 rows=138939341 width=8)
>                Filter: ((otid)::text !~~ 'ERROR%'::text)
>          ->  Hash  (cost=218057.87..218057.87 rows=174 width=32)

Well, it knows that it's going to be expensive (cost=5240444.80). Since
it thinks you'll only get 174 rows from the other side and 6016
matching, I can't see how an index could be calculated as more expensive.

Try issuing ENABLE seq_scan=off and re-running the EXPLAIN, let's see
what cost that comes up with.

Oh, and I take it sms_new is recently vacuumed and analysed?

--
   Richard Huxton
   Archonet Ltd