Thread: Please consider removing "select count(*)..."
In both pgAdmin 2 and 3, whenever I click on a table node in the object tree, for a table with relatively big number of rows, it's taking a very long time to update the property panel (up to a few minutes with very high CPU load by the postgres process) making practically impossible usage of the tool. The reason is in the "select count(*)" query pgAdmin use to get table row count. I am not sure whether there is another way to count table rows in Postgres, but I see it does not like 'select count(*)' much for large tables. I just commented out this query for myself forcing row count field to be always zero and it works just fine for me.
> -----Original Message----- > From: Yurgis Baykshtis [mailto:ybaykshtis@aurigin.com] > Sent: 23 May 2003 19:46 > To: pgadmin-hackers@postgresql.org > Subject: [pgadmin-hackers] Please consider removing "select > count(*)..." > > > In both pgAdmin 2 and 3, whenever I click on a table node in > the object tree, for a table with relatively big number of > rows, it's taking a very long time to update the property > panel (up to a few minutes with very high CPU load by the > postgres process) making practically impossible usage of the > tool. The reason is in the "select count(*)" query pgAdmin > use to get table row count. > I am not sure whether there is another way to count table > rows in Postgres, but I see it does not like 'select > count(*)' much for large tables. > > I just commented out this query for myself forcing row count > field to be always zero and it works just fine for me. Hi, In pgAdmin II, go to Tools -> Options -> PostgreSQL and switch off Auto Row Count. In pgAdmin III I notice the option is not there yet. I'll see if I can add it shortly. Regards, Dave.
> -----Original Message----- > From: Yurgis Baykshtis [mailto:ybaykshtis@aurigin.com] > Sent: 23 May 2003 19:46 > To: pgadmin-hackers@postgresql.org > Subject: [pgadmin-hackers] Please consider removing "select > count(*)..." > > > In both pgAdmin 2 and 3, whenever I click on a table node in > the object tree, for a table with relatively big number of > rows, it's taking a very long time to update the property > panel (up to a few minutes with very high CPU load by the > postgres process) making practically impossible usage of the > tool. The reason is in the "select count(*)" query pgAdmin > use to get table row count. > I am not sure whether there is another way to count table > rows in Postgres, but I see it does not like 'select > count(*)' much for large tables. > > I just commented out this query for myself forcing row count > field to be always zero and it works just fine for me. Hi Yurgis, I've now added an option for this to the pgAdmin III CVS. Regards, Dave.
Thanks, Dave. A similar issue exists in the data view again for both versions. pgAdmin II calculates number of rows first then asks for a limit. But as far as I understood, this will be eventually resolved once data views support cursors. -----Original Message----- From: Dave Page [mailto:dpage@vale-housing.co.uk] Sent: Friday, May 23, 2003 12:47 PM To: Yurgis Baykshtis; pgadmin-hackers@postgresql.org Subject: RE: [pgadmin-hackers] Please consider removing "select count(*)..." > -----Original Message----- > From: Yurgis Baykshtis [mailto:ybaykshtis@aurigin.com] > Sent: 23 May 2003 19:46 > To: pgadmin-hackers@postgresql.org > Subject: [pgadmin-hackers] Please consider removing "select > count(*)..." > > > In both pgAdmin 2 and 3, whenever I click on a table node in > the object tree, for a table with relatively big number of > rows, it's taking a very long time to update the property > panel (up to a few minutes with very high CPU load by the > postgres process) making practically impossible usage of the > tool. The reason is in the "select count(*)" query pgAdmin > use to get table row count. > I am not sure whether there is another way to count table > rows in Postgres, but I see it does not like 'select > count(*)' much for large tables. > > I just commented out this query for myself forcing row count > field to be always zero and it works just fine for me. Hi Yurgis, I've now added an option for this to the pgAdmin III CVS. Regards, Dave.
Dave Page wrote: > > >>-----Original Message----- >>From: Yurgis Baykshtis [mailto:ybaykshtis@aurigin.com] >>Sent: 23 May 2003 19:46 >>To: pgadmin-hackers@postgresql.org >>Subject: [pgadmin-hackers] Please consider removing "select >>count(*)..." >> >> >>In both pgAdmin 2 and 3, whenever I click on a table node in >>the object tree, for a table with relatively big number of >>rows, it's taking a very long time to update the property >>panel (up to a few minutes with very high CPU load by the >>postgres process) making practically impossible usage of the >>tool. The reason is in the "select count(*)" query pgAdmin >>use to get table row count. >>I am not sure whether there is another way to count table >>rows in Postgres, but I see it does not like 'select >>count(*)' much for large tables. >> >>I just commented out this query for myself forcing row count >>field to be always zero and it works just fine for me. >> >> > >Hi Yurgis, > >I've now added an option for this to the pgAdmin III CVS. > > Didn't think this would be an issue so early. I planned to implement this with a threshold level. We have rowsEstimated, which should (hopefully) be more or less up-to-date if VACUUMed properly, and if e.g. 100,000 rows (configurable) are exceeded a count(*) is suppressed and only performed on explicit refresh. Yurgis, you seem to have large tables, which default threshold seems reasonable to you? 10k rows? 100k? Regards, Andreas
> Yurgis, you seem to have large tables, which default threshold seems > reasonable to you? 10k rows? 100k? I have tables with just 200K rows. I did not really expect Postgres to perform so poorly. So, I think the lower the default threshold the better. -----Original Message----- From: Andreas Pflug [mailto:Andreas.Pflug@web.de] Sent: Friday, May 23, 2003 3:49 PM To: Dave Page; pgadmin-hackers@postgresql.org; Yurgis Baykshtis Subject: Re: [pgadmin-hackers] Please consider removing "select count(*)..." Dave Page wrote: > > >>-----Original Message----- >>From: Yurgis Baykshtis [mailto:ybaykshtis@aurigin.com] >>Sent: 23 May 2003 19:46 >>To: pgadmin-hackers@postgresql.org >>Subject: [pgadmin-hackers] Please consider removing "select >>count(*)..." >> >> >>In both pgAdmin 2 and 3, whenever I click on a table node in >>the object tree, for a table with relatively big number of >>rows, it's taking a very long time to update the property >>panel (up to a few minutes with very high CPU load by the >>postgres process) making practically impossible usage of the >>tool. The reason is in the "select count(*)" query pgAdmin >>use to get table row count. >>I am not sure whether there is another way to count table >>rows in Postgres, but I see it does not like 'select >>count(*)' much for large tables. >> >>I just commented out this query for myself forcing row count >>field to be always zero and it works just fine for me. >> >> > >Hi Yurgis, > >I've now added an option for this to the pgAdmin III CVS. > > Didn't think this would be an issue so early. I planned to implement this with a threshold level. We have rowsEstimated, which should (hopefully) be more or less up-to-date if VACUUMed properly, and if e.g. 100,000 rows (configurable) are exceeded a count(*) is suppressed and only performed on explicit refresh. Yurgis, you seem to have large tables, which default threshold seems reasonable to you? 10k rows? 100k? Regards, Andreas