Re: [HACKERS] Very poor estimates from planner

2003-11-09 Thread Rod Taylor
On Thu, 2003-11-06 at 10:35, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > >> -> Hash Join (cost=3D1230.79..60581.82 rows=3D158 width=3D54)= > > (actual time=3D1262.35..151200.29 rows=3D1121988 loops=3D1) > >> Hash Cond: ("outer".account_id =3D "inner".account_id) > >> -> Hash Join

[Fwd: Re: [HACKERS] Very poor estimates from planner]

2003-11-06 Thread Rod Taylor
On Thu, 2003-11-06 at 10:35, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > >> -> Hash Join (cost=3D1230.79..60581.82 rows=3D158 width=3D54)= > > (actual time=3D1262.35..151200.29 rows=3D1121988 loops=3D1) > >> Hash Cond: ("outer".account_id =3D "inner".account_id) > >> -> Hash Join

Re: [HACKERS] Very poor estimates from planner

2003-11-06 Thread Tom Lane
Rod Taylor <[EMAIL PROTECTED]> writes: >> -> Hash Join (cost=3D1230.79..60581.82 rows=3D158 width=3D54)= > (actual time=3D1262.35..151200.29 rows=3D1121988 loops=3D1) >> Hash Cond: ("outer".account_id =3D "inner".account_id) >> -> Hash Join (cost=3D1226.78..52863.43 rows=3D1542558 w= > idth=3D

Re: [HACKERS] Very poor estimates from planner

2003-11-05 Thread Rod Taylor
On Wed, 2003-11-05 at 19:18, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > Effectively, the planner has amazingly inaccurate row estimates. > > It seems the key estimation failure is in this join step: > > -> Hash Join (cost=1230.79..60581.82 rows=158 width=54) (actual

Re: [HACKERS] Very poor estimates from planner

2003-11-05 Thread scott.marlowe
On Wed, 5 Nov 2003, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > >> I'm not sure if that will actually change the default_statistics_target > > > Hmm.. I was under the impression that it would work for any tables that > > haven't otherwise been overridden. > > It will. I think Sco

Re: [HACKERS] Very poor estimates from planner

2003-11-05 Thread Tom Lane
Rod Taylor <[EMAIL PROTECTED]> writes: > Effectively, the planner has amazingly inaccurate row estimates. It seems the key estimation failure is in this join step: -> Hash Join (cost=1230.79..60581.82 rows=158 width=54) (actual time=1262.35..151200.29 rows=1121988 loops=1)

Re: [HACKERS] Very poor estimates from planner

2003-11-05 Thread Rod Taylor
On Wed, 2003-11-05 at 18:57, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > >> I'm not sure if that will actually change the default_statistics_target > > > Hmm.. I was under the impression that it would work for any tables that > > haven't otherwise been overridden. > > It will. I t

Re: [HACKERS] Very poor estimates from planner

2003-11-05 Thread Tom Lane
Rod Taylor <[EMAIL PROTECTED]> writes: >> I'm not sure if that will actually change the default_statistics_target > Hmm.. I was under the impression that it would work for any tables that > haven't otherwise been overridden. It will. I think Scott is recalling the original circa-7.2 implementati

Re: [HACKERS] Very poor estimates from planner

2003-11-05 Thread Rod Taylor
> I'm not sure if that will actually change the default_statistics_target of > the tables you're analyzing, I think it will only apply to newly created > tables. > > I believe you have to alter table alter column set statistics 1000 for > each column you want a statistic of 1000. You might w

Re: [HACKERS] Very poor estimates from planner

2003-11-05 Thread scott.marlowe
On Wed, 5 Nov 2003, Rod Taylor wrote: > Since this is a large query, attachments for the explains / query. > > Configuration: > dev_iqdb=# select version(); > version >

[HACKERS] Very poor estimates from planner

2003-11-05 Thread Rod Taylor
Since this is a large query, attachments for the explains / query. Configuration: dev_iqdb=# select version(); version PostgreSQL 7.4beta1 on i386-portbld-freeb