phpPgAdmin

>   Does anyone object to the addition of an 'Estimated Row Count'
> column on the tables page? (using pg_class.reltuples)

Well, is there any point?  I'm not opposed, but why....?

Christopher Kings-Lynne wrote:
>>   Does anyone object to the addition of an 'Estimated Row Count'
>> column on the tables page? (using pg_class.reltuples)
> 
> 
> Well, is there any point?  I'm not opposed, but why....?
> 

Well, I thought that'd be obvious, for an estimated row count
of course ;)

I've just started using Slony-I for replication and found it
to be a handy quick way of checking that replication was
actually happening on several tables.
You have to analyze the tables first to get the real count,
but then thats why it's an estimated.

I'll implemented for my own use, and i'm finding it quite handy,
just thought others may like it. I'm sure there must be other
uses too. :)

Yeah, just thought of one - it would be useful when you're
unfamiliar with a db schema, it gives an instant idea of how
big each table is.

-- 
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

> I've just started using Slony-I for replication and found it
> to be a handy quick way of checking that replication was
> actually happening on several tables.
> You have to analyze the tables first to get the real count,
> but then thats why it's an estimated.
> 
> I'll implemented for my own use, and i'm finding it quite handy,
> just thought others may like it. I'm sure there must be other
> uses too. :)
> 
> Yeah, just thought of one - it would be useful when you're
> unfamiliar with a db schema, it gives an instant idea of how
> big each table is.

Go ahead then, just make it clear that it's an estimate...otherwise we 
will have confused noobs.

Chris

Alle 07:22, venerd=EC 8 ottobre 2004, Christopher Kings-Lynne ha scritto:
> > I've just started using Slony-I for replication and found it
> > to be a handy quick way of checking that replication was
> > actually happening on several tables.
> > You have to analyze the tables first to get the real count,
> > but then thats why it's an estimated.
> >
> > I'll implemented for my own use, and i'm finding it quite handy,
> > just thought others may like it. I'm sure there must be other
> > uses too. :)
> >
> > Yeah, just thought of one - it would be useful when you're
> > unfamiliar with a db schema, it gives an instant idea of how
> > big each table is.
>
> Go ahead then, just make it clear that it's an estimate...otherwise we
> will have confused noobs.

=46or a new table (and always before the first vacuum, I suppose) the estim=
ated=20
row count is 1000 (!). Is there anything we can do about that?

Nicola

Nicola Soranzo wrote:
> For a new table (and always before the first vacuum, I suppose) the estimated 
> row count is 1000 (!). Is there anything we can do about that?

We could execute analyze after a table is created with phpPgAdmin,
or would this have adverse effects on query planning?
Is there a reason that the estimated row count is set to 1000 by
PostgreSQL?

-- 
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

On Monday 11 October 2004 12:06, Mark Gibson wrote:
> Nicola Soranzo wrote:
> > For a new table (and always before the first vacuum, I suppose) the
> > estimated row count is 1000 (!). Is there anything we can do about that?
>
> We could execute analyze after a table is created with phpPgAdmin,
> or would this have adverse effects on query planning?
> Is there a reason that the estimated row count is set to 1000 by
> PostgreSQL?

It *could* have adverse effects on query planning but if it does then that is 
a bug in the planner, so I think we can discount that argument.  I think the 
bigger problem is that tables that grow / shrink which makes the numbers get 
all out of whack without any way to warn the users.  This is one of the 
reasons we hadn't done this in the past.

FWIW pgadmin sets a value in its config file that acts as a switch between 
true count and estimated count, with the theory that on smaller tables you 
might was well run a real count.  That doesn't seem to solve the 
mis-estimation on larger tables though, I've sent an email to Dave to see if 
they've tried to tackle that problem.

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

> FWIW pgadmin sets a value in its config file that acts as a switch between 
> true count and estimated count, with the theory that on smaller tables you 
> might was well run a real count.  That doesn't seem to solve the 
> mis-estimation on larger tables though, I've sent an email to Dave to see if 
> they've tried to tackle that problem.

The other way to do it of course is to have a button you can push to 
view the count on a different page.  That way you can request the slow 
query whenever you want.

Chris

On Monday 11 October 2004 17:35, Robert Treat wrote:
> On Monday 11 October 2004 12:06, Mark Gibson wrote:
> > Nicola Soranzo wrote:
> > > For a new table (and always before the first vacuum, I suppose) the
> > > estimated row count is 1000 (!). Is there anything we can do about
> > > that?
> >
> > We could execute analyze after a table is created with phpPgAdmin,
> > or would this have adverse effects on query planning?
> > Is there a reason that the estimated row count is set to 1000 by
> > PostgreSQL?
>
> It *could* have adverse effects on query planning but if it does then that
> is a bug in the planner, so I think we can discount that argument.  I think
> the bigger problem is that tables that grow / shrink which makes the
> numbers get all out of whack without any way to warn the users.  This is
> one of the reasons we hadn't done this in the past.
>
> FWIW pgadmin sets a value in its config file that acts as a switch between
> true count and estimated count, with the theory that on smaller tables you
> might was well run a real count.  That doesn't seem to solve the
> mis-estimation on larger tables though, I've sent an email to Dave to see
> if they've tried to tackle that problem.

Just to follow up, the PgAdmin folks say that they just live with the 
estimation issue.  I do think that we could run analyze after phppgadmin 
table creation without much issue.  Does anyone have any feelings about using 
a setting to determine when to switch from count(*) to reltuples? Ie. if 
reltuples comes back at less than say 2000, we will issue a count(*) on the 
table for exact amounts. Granted this would be slower than just reltuples, 
but perhaps not too slow?  

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

=D0=92 =D0=A1=D1=80=D0=B4, 13.10.2004, =D0=B2 15:06, Robert Treat =D0=BF=D0=
=B8=D1=88=D0=B5=D1=82:

> Just to follow up, the PgAdmin folks say that they just live with the=20
> estimation issue.  I do think that we could run analyze after phppgadmin=20
> table creation without much issue.  Does anyone have any feelings about u=
sing=20
> a setting to determine when to switch from count(*) to reltuples? Ie. if=20
> reltuples comes back at less than say 2000, we will issue a count(*) on t=
he=20
> table for exact amounts. Granted this would be slower than just reltuples=
,=20
> but perhaps not too slow? =20

That sounds like a good idea, just please don't make that number
configurable.

Thanks

--=20
Markus Bertheau <twanger@...>

On Wednesday 13 October 2004 14:42, Markus Bertheau wrote:
> =D0=92 =D0=A1=D1=80=D0=B4, 13.10.2004, =D0=B2 15:06, Robert Treat =D0=BF=
=D0=B8=D1=88=D0=B5=D1=82:
> > Just to follow up, the PgAdmin folks say that they just live with the
> > estimation issue.  I do think that we could run analyze after phppgadmin
> > table creation without much issue.  Does anyone have any feelings about
> > using a setting to determine when to switch from count(*) to reltuples?
> > Ie. if reltuples comes back at less than say 2000, we will issue a
> > count(*) on the table for exact amounts. Granted this would be slower
> > than just reltuples, but perhaps not too slow?
>
> That sounds like a good idea, just please don't make that number
> configurable.
>

Well, different hardware and different schemas would dictate that there cou=
ld=20
be no one size fits all setting, so if you don't make it configureable you'=
ll=20
just end up telling people to hack the source. Do you see that as a good=20
thing?=20

=2D-=20
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

>>Just to follow up, the PgAdmin folks say that they just live with the 
>>estimation issue.  I do think that we could run analyze after phppgadmin 
>>table creation without much issue.  Does anyone have any feelings about using 
>>a setting to determine when to switch from count(*) to reltuples? Ie. if 
>>reltuples comes back at less than say 2000, we will issue a count(*) on the 
>>table for exact amounts. Granted this would be slower than just reltuples, 
>>but perhaps not too slow?  
> 
> 
> That sounds like a good idea, just please don't make that number
> configurable.

It's a bad idea because it's a _mega_ slowdown.  With the estimated 
value, you can get all tables and all their estimated values in one go.

With doing counts, you potentially have to query for all tables plus 
estimates, then do 'n' separate count queries, one for each table.

I'm not sure about doing an analyze after table creation - who are we to 
second guess the years of experience of the postgresql developers?

Chris

=D0=92 =D0=A7=D1=82=D0=B2, 14.10.2004, =D0=B2 04:15, Robert Treat =D0=BF=D0=
=B8=D1=88=D0=B5=D1=82:
> On Wednesday 13 October 2004 14:42, Markus Bertheau wrote:
> > =D0=92 =D0=A1=D1=80=D0=B4, 13.10.2004, =D0=B2 15:06, Robert Treat =D0=
=BF=D0=B8=D1=88=D0=B5=D1=82:
> > > Just to follow up, the PgAdmin folks say that they just live with the
> > > estimation issue.  I do think that we could run analyze after phppgad=
min
> > > table creation without much issue.  Does anyone have any feelings abo=
ut
> > > using a setting to determine when to switch from count(*) to reltuple=
s?
> > > Ie. if reltuples comes back at less than say 2000, we will issue a
> > > count(*) on the table for exact amounts. Granted this would be slower
> > > than just reltuples, but perhaps not too slow?
> >
> > That sounds like a good idea, just please don't make that number
> > configurable.
> >
>=20
> Well, different hardware and different schemas would dictate that there c=
ould=20
> be no one size fits all setting, so if you don't make it configureable yo=
u'll=20
> just end up telling people to hack the source. Do you see that as a good=20
> thing?=20

With that argument you'd need that setting configurable per server /
database / schema.

I'm out.

--=20
Markus Bertheau <twanger@...>

>>Well, different hardware and different schemas would dictate that there could 
>>be no one size fits all setting, so if you don't make it configureable you'll 
>>just end up telling people to hack the source. Do you see that as a good 
>>thing? 
> 
> 
> With that argument you'd need that setting configurable per server /
> database / schema.
> 
> I'm out.

You're out?

=D0=92 =D0=A7=D1=82=D0=B2, 14.10.2004, =D0=B2 09:15, Christopher Kings-Lynn=
e =D0=BF=D0=B8=D1=88=D0=B5=D1=82:
> >>Well, different hardware and different schemas would dictate that there=
 could=20
> >>be no one size fits all setting, so if you don't make it configureable =
you'll=20
> >>just end up telling people to hack the source. Do you see that as a goo=
d=20
> >>thing?=20
> >=20
> >=20
> > With that argument you'd need that setting configurable per server /
> > database / schema.
> >=20
> > I'm out.
>=20
> You're out?

Yes, I'm no longer fighting this (my) fight about my view of usability
on this list.

--=20
Markus Bertheau <twanger@...>

Robert Treat wrote:
> Just to follow up, the PgAdmin folks say that they just live with the 
> estimation issue.  I do think that we could run analyze after phppgadmin 
> table creation without much issue.  Does anyone have any feelings about using 
> a setting to determine when to switch from count(*) to reltuples? Ie. if 
> reltuples comes back at less than say 2000, we will issue a count(*) on the 
> table for exact amounts. Granted this would be slower than just reltuples, 
> but perhaps not too slow?  

I'm not sure there is much to be gained by this, we would need to 
indicate which counts are estimates and which are actual count(*)'s.
I think we should have just one or the other, and seeing as count(*)'ing
all table is just impractical, it leaves us with the first option.

-- 
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

Alle 10:05, gioved=EC 14 ottobre 2004, Mark Gibson ha scritto:
> Robert Treat wrote:
> > Just to follow up, the PgAdmin folks say that they just live with the
> > estimation issue.  I do think that we could run analyze after phppgadmin
> > table creation without much issue.  Does anyone have any feelings about
> > using a setting to determine when to switch from count(*) to reltuples?
> > Ie. if reltuples comes back at less than say 2000, we will issue a
> > count(*) on the table for exact amounts. Granted this would be slower
> > than just reltuples, but perhaps not too slow?
>
> I'm not sure there is much to be gained by this, we would need to
> indicate which counts are estimates and which are actual count(*)'s.
> I think we should have just one or the other, and seeing as count(*)'ing
> all table is just impractical, it leaves us with the first option.

Also for me we should show estimated values or (maybe better) nothing, ie=20
remove the column and the problem.

Nicola

> Also for me we should show estimated values or (maybe better) nothing, ie 
> remove the column and the problem.

And we come back to the original situation of us being far better of 
simply not showing row counts at all - I think that's still the simplest 
and most clear situation.

Chris

Alle 03:00, venerd=EC 15 ottobre 2004, Christopher Kings-Lynne ha scritto:
> > Also for me we should show estimated values or (maybe better) nothing, =
ie
> > remove the column and the problem.
>
> And we come back to the original situation of us being far better of
> simply not showing row counts at all - I think that's still the simplest
> and most clear situation.

Before the release, we must remove row counts or fix the problem!

Nicola

>>And we come back to the original situation of us being far better of
>>simply not showing row counts at all - I think that's still the simplest
>>and most clear situation.
> 
> Before the release, we must remove row counts or fix the problem!

I vote for removing it...

Christopher Kings-Lynne wrote:
>>> And we come back to the original situation of us being far better of
>>> simply not showing row counts at all - I think that's still the simplest
>>> and most clear situation.
>>
>> Before the release, we must remove row counts or fix the problem!
> 
> I vote for removing it...

I know this is going to be frowned upon by some,
but how about a config var to enable it.
We leave it disabled by default, and explain the issue within
the default config file, so that the admin can decide whether
to open the can of worms or not.

Otherwise, just remove it!

-- 
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.

On Wednesday 03 November 2004 05:35, Mark Gibson wrote:
> Christopher Kings-Lynne wrote:
> >>> And we come back to the original situation of us being far better of
> >>> simply not showing row counts at all - I think that's still the
> >>> simplest and most clear situation.
> >>
> >> Before the release, we must remove row counts or fix the problem!
> >
> > I vote for removing it...
>
> I know this is going to be frowned upon by some,
> but how about a config var to enable it.
> We leave it disabled by default, and explain the issue within
> the default config file, so that the admin can decide whether
> to open the can of worms or not.
>

I'm not against this idea.

> Otherwise, just remove it!

Course I'm not against this one either :-)

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL