From: | Sean Leach <sleach(at)wiggum(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Weird issue with planner choosing seq scan |
Date: | 2008-02-24 20:28:17 |
Message-ID: | FCB9D20E-A0B7-4FAE-AC22-703C1BCCEFE7@wiggum.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Denne | 2008-02-24 21:18:02 | Re: Weird issue with planner choosing seq scan |
Previous Message | Tom Lane | 2008-02-24 19:10:25 | Re: Weird issue with planner choosing seq scan |