Обсуждение: Very slow viewing of large table.
Hello. When in PgAdmin3 I click on a large table, it waits before opening it. As far as I could understand, it issues a row count on a table. Is there way to stop this behaviour? I found a letter about the same problem of February 2002, and Dave Page wrote he was intending to do something about. Am I searching in the wrong place or there is no capability for cancelling count queries? -- Constantin Stefanov
> -----Original Message----- > From: Constantin Stefanov [mailto:cstef@mail.ru] > Sent: 22 October 2003 14:53 > To: pgadmin-support@postgresql.org > Subject: [pgadmin-support] Very slow viewing of large table. > > Hello. > > When in PgAdmin3 I click on a large table, it waits before > opening it. > As far as I could understand, it issues a row count on a > table. Is there way to stop this behaviour? > I found a letter about the same problem of February 2002, and > Dave Page wrote he was intending to do something about. > Am I searching in the wrong place or there is no capability > for cancelling count queries? Under File -> Options -> Query there is an option to control this behaviour, though unless you have a slow server, it should have a sensible value following install. From the helpfile: "Count rows if estimated less than" - In the object browser, if the estimated number of rows in a table (as read from the table statistics) is below this limit, a SELECT count(*) will be performed to find the exact value. Regards, Dave.
Dave Page wrote: >>-----Original Message----- >>From: Constantin Stefanov [mailto:cstef@mail.ru] >>Sent: 22 October 2003 14:53 >>To: pgadmin-support@postgresql.org >>Subject: [pgadmin-support] Very slow viewing of large table. >> >>When in PgAdmin3 I click on a large table, it waits before >>opening it. >>As far as I could understand, it issues a row count on a >>table. Is there way to stop this behaviour? >>I found a letter about the same problem of February 2002, and >>Dave Page wrote he was intending to do something about. >>Am I searching in the wrong place or there is no capability >>for cancelling count queries? > > Under File -> Options -> Query there is an option to control this > behaviour, though unless you have a slow server, it should have a > sensible value following install. From the helpfile: > > "Count rows if estimated less than" - In the object browser, if the > estimated number of rows in a table (as read from the table statistics) > is below this limit, a SELECT count(*) will be performed to find the > exact value. Thanks, I set that value to 0, and now I have the desired behaviour. But, for that table the values are: Rows(estimated) - 7 Rows(counted) - 77204608 I think the difference is too large. From the debug output I could not understand the query which pg_admin issues to have the value for estimated number of rows. Could you show me that query so I could give it directly from psql to see whether there is an error in my database stats or there is a bug in pg_admin when interpreting query result? -- Constantin Stefanov
> -----Original Message----- > From: Constantin Stefanov [mailto:cstef@mail.ru] > Sent: 23 October 2003 07:30 > To: pgadmin-support@postgresql.org > Subject: Re: [pgadmin-support] Very slow viewing of large table. > > Thanks, I set that value to 0, and now I have the desired behaviour. > But, for that table the values are: > Rows(estimated) - 7 > Rows(counted) - 77204608 > I think the difference is too large. From the debug output I > could not understand the query which pg_admin issues to have > the value for estimated number of rows. Could you show me > that query so I could give it directly from psql to see > whether there is an error in my database stats or there is a > bug in pg_admin when interpreting query result? It works fine for me, so the query is probably right. When did you last run VACUUM ANALYZE on that table or database? The value comes from pg_class.reltuples which is updated by VACUUM ANALYZE: SELECT relname, reltuples FROM pg_class; Regards, Dave.
Dave Page wrote: >>Thanks, I set that value to 0, and now I have the desired behaviour. >>But, for that table the values are: >>Rows(estimated) - 7 >>Rows(counted) - 77204608 >>I think the difference is too large. From the debug output I >>could not understand the query which pg_admin issues to have >>the value for estimated number of rows. Could you show me >>that query so I could give it directly from psql to see >>whether there is an error in my database stats or there is a >>bug in pg_admin when interpreting query result? > > It works fine for me, so the query is probably right. When did you last > run VACUUM ANALYZE on that table or database? I have VACCUM ANALYZE every nignt and VACCUM FULL every week. > The value comes from pg_class.reltuples which is updated by VACUUM > ANALYZE: > > SELECT relname, reltuples FROM pg_class; For my table there is ------------------------------ accounting# SELECT relname, reltuples FROM pg_class where relname='ip_accounting'; relname | reltuples ---------------+------------- ip_accounting | 7.71493e+07 ------------------------------ May be there is some overflow or incorrect result parsing? -- Constantin Stefanov
> -----Original Message----- > From: Constantin Stefanov [mailto:cstef@mail.ru] > Sent: 23 October 2003 08:59 > To: pgadmin-support@postgresql.org > Subject: Re: [pgadmin-support] Very slow viewing of large table. > > > SELECT relname, reltuples FROM pg_class; > For my table there is > ------------------------------ > accounting# SELECT relname, reltuples FROM pg_class where > relname='ip_accounting'; > relname | reltuples > ---------------+------------- > ip_accounting | 7.71493e+07 > ------------------------------ > May be there is some overflow or incorrect result parsing? Hmm, shouldn't be - the code appears to use longs everywhere. I can't really spend time on this now though - Andreas, can you take a look please? Thanks, Dave.
Dave Page wrote: > > > > >>-----Original Message----- >>From: Constantin Stefanov [mailto:cstef@mail.ru] >>Sent: 23 October 2003 08:59 >>To: pgadmin-support@postgresql.org >>Subject: Re: [pgadmin-support] Very slow viewing of large table. >> >> >> >>>SELECT relname, reltuples FROM pg_class; >>> >>> >>For my table there is >>------------------------------ >>accounting# SELECT relname, reltuples FROM pg_class where >>relname='ip_accounting'; >> relname | reltuples >>---------------+------------- >> ip_accounting | 7.71493e+07 >>------------------------------ >>May be there is some overflow or incorrect result parsing? >> >> > >Hmm, shouldn't be - the code appears to use longs everywhere. I can't >really spend time on this now though - Andreas, can you take a look >please? > > It's not an overflow, instead the column is scanned as int, resulting in reltuples=7 (which isn't too precise :-) I changed the types of rows to wxLongLong, and estimatedRows to double now. Regards, Andreas
> -----Original Message----- > From: Andreas Pflug [mailto:pgadmin@pse-consulting.de] > Sent: 23 October 2003 11:51 > To: Dave Page > Cc: Constantin Stefanov; pgadmin-support@postgresql.org > Subject: Re: [pgadmin-support] Very slow viewing of large table. > > Dave Page wrote: > > > > > > > > > > >>-----Original Message----- > >>From: Constantin Stefanov [mailto:cstef@mail.ru] > >>Sent: 23 October 2003 08:59 > >>To: pgadmin-support@postgresql.org > >>Subject: Re: [pgadmin-support] Very slow viewing of large table. > >> > >> > >> > >>>SELECT relname, reltuples FROM pg_class; > >>> > >>> > >>For my table there is > >>------------------------------ > >>accounting# SELECT relname, reltuples FROM pg_class where > >>relname='ip_accounting'; > >> relname | reltuples > >>---------------+------------- > >> ip_accounting | 7.71493e+07 > >>------------------------------ > >>May be there is some overflow or incorrect result parsing? > >> > >> > > > >Hmm, shouldn't be - the code appears to use longs > everywhere. I can't > >really spend time on this now though - Andreas, can you take a look > >please? > > > > > It's not an overflow, instead the column is scanned as int, > resulting in > reltuples=7 (which isn't too precise :-) I changed the types > of rows to wxLongLong, and estimatedRows to double now. Of course (kicking myself now...) Regards, Dave.