On Feb 24, 2008, at 11:10 AM, Tom Lane wrote:
Sean Leach <sleach@xxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
