Re: Weird issue with planner choosing seq scan

On Feb 24, 2008, at 11:10 AM, Tom Lane wrote:

> Sean Leach <sleach@wiggum.com> writes:
>> Now - here is prod:
>
>> db=> select count(1) from u_counts;
>>   count
>> ---------
>>  3292215
>> (1 row)
>
>
>>          ->  Seq Scan on u_counts c  (cost=0.00..444744.45
>> rows=1106691 width=4) (actual time=1429.996..7893.178 rows=1036015
>> loops=1)
>>                Filter: (stamp > (now() - '1 day'::interval))
>
> Given that this scan actually is selecting about a third of the table,
> I'm not sure that the planner is doing the wrong thing.  It's hard to
> see how an indexscan would be an improvement.
>
> [ thinks for a bit... ]  Actually, the problem might be the 3M
> executions of now() and interval subtraction that you get in the
> seqscan
> case.  What results do you get if you write it with a sub-select
> like this:
>
> explain analyze SELECT node,count(*) AS counts FROM u_counts
> c,res r WHERE c.res_id=r.id AND stamp > (SELECT current_timestamp -
> interval
> '1 day') AND r.rtype='udns' AND r.location=1 GROUP BY node;


Unfortunately, the same, dev uses index scan, prod uses seq scan, prod
takes about 4x longer to do the query.  Any other thoughts on best way
to proceed?   Thanks again Tom.




>
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match