Re: Weird issue with planner choosing seq scan

Nope, seems like that would make sense but dev is 10 mill, prod is 3
million. Also including random_page_cost below. Thanks for any help.

Here is dev:

db=> analyze u_counts;
ANALYZE
Time: 15775.161 ms

db=> select count(1) from u_counts;
count
----------
10972078
(1 row)

db=> show random_page_cost;
random_page_cost
------------------
4
(1 row)

Time: 0.543 ms
db=> explain analyze SELECT node,count(*) AS counts FROM u_counts
c,res r WHERE c.res_id=r.id AND stamp > (current_timestamp - interval
'1 day') AND r.rtype='udns' AND r.location=1 GROUP BY node;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=12906.12..12906.24 rows=9 width=6) (actual
time=3135.831..3135.845 rows=9 loops=1)
-> Hash Join (cost=10.42..12538.88 rows=73449 width=6) (actual
time=0.746..2475.632 rows=391380 loops=1)
Hash Cond: (c.res_id = r.id)
-> Index Scan using u_counts_i2 on db c
(cost=0.00..10882.33 rows=243105 width=4) (actual time=0.287..1269.651
rows=391380 loops=1)
Index Cond: (stamp > (now() - '1 day'::interval))
-> Hash (cost=9.53..9.53 rows=71 width=10) (actual
time=0.430..0.430 rows=78 loops=1)
-> Seq Scan on res r (cost=0.00..9.53 rows=71
width=10) (actual time=0.021..0.203 rows=78 loops=1)
Filter: (((rtype)::text = 'udns'::text) AND
(location = 1))
Total runtime: 3136.000 ms

Now - here is prod:

db=> show random_page_cost;
random_page_cost
------------------
4
(1 row)

Time: 0.434 ms

db=> analyze u_counts;
ANALYZE
Time: 179.928 ms

db=> select count(1) from u_counts;
count
---------
3292215
(1 row)

db=> explain analyze SELECT node,count(*) AS counts FROM u_counts
c,res r WHERE c.res_id=r.id AND stamp > (current_timestamp - interval
'1 day') AND r.rtype='udns' AND r.location=1 GROUP BY node;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=452333.49..452333.59 rows=8 width=6) (actual
time=13200.887..13200.902 rows=9 loops=1)
-> Hash Join (cost=16.71..451192.74 rows=228149 width=6) (actual
time=1430.458..11274.073 rows=1036015 loops=1)
Hash Cond: (c.res_id = r.id)
-> 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))
-> Hash (cost=15.88..15.88 rows=67 width=10) (actual
time=0.363..0.363 rows=60 loops=1)
-> Seq Scan on res r (cost=0.00..15.88 rows=67
width=10) (actual time=0.046..0.258 rows=60 loops=1)
Filter: (((rtype)::text = 'udns'::text) AND
(location = 1))
Total runtime: 13201.046 ms
(9 rows)

Time: 13204.686 ms

On Feb 24, 2008, at 9:50 AM, Tom Lane wrote:

> Sean Leach <sleach(at)wiggum(dot)com> writes:
>> I have a table, that in production, currently has a little over 3
>> million records in production. In development, the same table has
>> about 10 million records (we have cleaned production a few weeks
>> ago).
>
> You mean the other way around, to judge by the rowcounts from EXPLAIN.
>
>> -> Index Scan using u_counts_i2 on u_counts c
>> (cost=0.00..53.53 rows=1082 width=4) (actual time=0.277..1224.582
>> rows=392173 loops=1)
>
> I kinda think the devel system wouldn't be using an indexscan e ither
> if it had up-to-date ANALYZE statistics. But even with the 1082 row
> estimate that seems a remarkably low cost estimate. Have you been
> playing games with random_page_cost? Maybe you forgot to duplicate
> the
> devel system's cost parameters onto the production system?
>
> regards, tom lane