Re: Weird issue with planner choosing seq scan

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

> Sean Leach <sleach(at)wiggum(dot)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