Thread: pgadmin3 and partitionned tables
Hi, I've got a partitionned table with PG 8.1, and I'm using pgadmin3 1.4.1. I'm having problems using pgadmin because it tries to count the elements of the partitionned table... The main table contains 0 record (so the stats say 0), so pgadmin decides it can count all the elements from the table... but the count() on this table is avout 40 000 000 record including all the partitions. The consequence is that everytime I click on the main table, pgadmin scans all the partitions (taking about 3 minutes and slowing down the server...) Is this a known problem ? Regards
Marc Cousin wrote: > Hi, > > I've got a partitionned table with PG 8.1, and I'm using pgadmin3 1.4.1. > I'm having problems using pgadmin because it tries to count the elements of > the partitionned table... > > The main table contains 0 record (so the stats say 0), so pgadmin decides it > can count all the elements from the table... but the count() on this table is > avout 40 000 000 record including all the partitions. The consequence is that > everytime I click on the main table, pgadmin scans all the partitions > (taking about 3 minutes and slowing down the server...) > > Is this a known problem ? Yes. You clearly ignored the Guru's hint to vacuum. Regards, Andreas
all the databases of the cluster are regularly vacuumed (at least once a day), all the stats are up to date. what i'm talking about is a partitionned table (8.1, with constraint exclusions), a 'main' table empty, and a dozen of inherited tables, which contain the real data. The main table contains 0 record, only the inheriting tables contain data. So the stats are right saying that the main table contains 0 record. Then, seeing that, pgadmin decides to count the record (as there aren't supposed to be a lot of them), but gets to read millions of record (the content of all the partitions). On Monday 10 April 2006 09:54, Andreas Pflug wrote: > Marc Cousin wrote: > > Hi, > > > > I've got a partitionned table with PG 8.1, and I'm using pgadmin3 1.4.1. > > I'm having problems using pgadmin because it tries to count the elements > > of the partitionned table... > > > > The main table contains 0 record (so the stats say 0), so pgadmin decides > > it can count all the elements from the table... but the count() on this > > table is avout 40 000 000 record including all the partitions. The > > consequence is that everytime I click on the main table, pgadmin scans > > all the partitions (taking about 3 minutes and slowing down the > > server...) > > > > Is this a known problem ? > > Yes. You clearly ignored the Guru's hint to vacuum. > > Regards, > Andreas
Marc Cousin wrote:
> all the databases of the cluster are regularly vacuumed (at least once a day),
> all the stats are up to date.
If stats say 0 ("estimated rows") rows but 40M rows are present stats
are clearly not up-to-date. We had interpretation problems of
pg_class.reltuples because it was read as int, not as float, but that
was fare earlier than 1.4.
If SELECT reltuples FROM pg_class WHERE relname='<foo>' returns
non-zero, but estimated rows is 0, your platform's strtod might have a
locale problem, but I doubt that because from my observations pgsql will
always return [1-9].[0-9](n)e[1-9](n), i.e. if the decimal point was the
problem est. rowcount would be between 1 and 9.
Regards,
Andreas
On Monday 10 April 2006 15:18, you wrote:
> Marc Cousin wrote:
> > all the databases of the cluster are regularly vacuumed (at least once a
> > day), all the stats are up to date.
>
> If stats say 0 ("estimated rows") rows but 40M rows are present stats
> are clearly not up-to-date. We had interpretation problems of
> pg_class.reltuples because it was read as int, not as float, but that
> was fare earlier than 1.4.
> If SELECT reltuples FROM pg_class WHERE relname='<foo>' returns
> non-zero, but estimated rows is 0, your platform's strtod might have a
> locale problem, but I doubt that because from my observations pgsql will
> always return [1-9].[0-9](n)e[1-9](n), i.e. if the decimal point was the
> problem est. rowcount would be between 1 and 9.
>
0 rows are effectively present in the table. That's the whole point...
The table contains 0 rows, but there are several tables that inherit from this
one. And those table contain the real rows.
So the stats say 0 rows on the main table, and they are right.
But pgadmin does a select count on this table, so postgres sums the rows from
this table and all the inheriting tables too.
Always stay on the list, private mail frequently gets dropped!!! > The table contains 0 rows, but there are several tables that inherit from this > one. And those table contain the real rows. Ok, you didn't mention inheritance, now I see the problem. There's only the solution to set the option "count rows if est. rowcount < -1" at the moment. I don't see a good final solution, maybe not counting if inherited tables are present regardless of reltuples? Regards, Andreas
On Monday 10 April 2006 15:37, Andreas Pflug wrote: > Always stay on the list, private mail frequently gets dropped!!! Yeah, my mistake :) > > > The table contains 0 rows, but there are several tables that inherit from > > this one. And those table contain the real rows. > > Ok, you didn't mention inheritance, now I see the problem. > There's only the solution to set the option "count rows if est. rowcount > < -1" at the moment. I don't see a good final solution, maybe not > counting if inherited tables are present regardless of reltuples? I'd think it would be better never to count if there are inherited tables, as it will frequently be because of partitionning so most of the time, there will be plenty of records. I'll use the workaround for now... thanks a lot