[PERFORM] Slow Select count(*) ... query on table with 60 Mio. rows

Hi, === Problem === i have a db-table data_measurand with about 6000 (60 Millions) rows and the following query takes about 20-30 seconds (with psql): mydb=# select count(*) from data_measurand; count -- 60846187 (1 row) === Question === - What can i do to improve the

Re: [PERFORM] Slow Select count(*) ... query on table with 60 Mio. rows

On Thu, 14 Jan 2010, tom wrote: i have a db-table data_measurand with about 6000 (60 Millions) rows and the following query takes about 20-30 seconds (with psql): mydb=# select count(*) from data_measurand; count -- 60846187 (1 row) Sounds pretty reasonable to me. Looking at your

Re: [PERFORM] Slow Select count(*) ... query on table with 60 Mio. rows

In response to tom : Hi, === Problem === i have a db-table data_measurand with about 6000 (60 Millions) rows and the following query takes about 20-30 seconds (with psql): mydb=# select count(*) from data_measurand; count -- 60846187 (1 row) === Question ===

Re: [PERFORM] Slow Select count(*) ... query on table with 60 Mio. rows

Matthew Wakeling matt...@flymine.org wrote: This is an FAQ. I just added it to the wiki FAQ page: http://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make

Re: [PERFORM] Slow Select count(*) ... query on table with 60 Mio. rows

Kevin Grittner wrote: Matthew Wakeling matt...@flymine.org wrote: This is an FAQ. I just added it to the wiki FAQ page: http://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F Maybe you could add a short note why an estimation like from the

Re: [PERFORM] Slow Select count(*) ... query on table with 60 Mio. rows

Ivan Voras ivo...@freebsd.org wrote: Maybe you could add a short note why an estimation like from the pg_class table is usually enough. OK. Will do. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription:

Re: [PERFORM] Slow Select count(*) ... query on table with 60 Mio. rows

Kevin Grittner wrote: Matthew Wakeling matt...@flymine.org wrote: This is an FAQ. I just added it to the wiki FAQ page: http://wiki.postgresql.org/wiki/FAQ#Why_is_.22SELECT_count.28.2A.29_FROM_bigtable.3B.22_slow.3F The content was already there, just not linked into the main

Re: [PERFORM] Slow Select count(*) ... query on table with 60 Mio. rows

Greg Smith g...@2ndquadrant.com wrote: The content was already there, just not linked into the main FAQ yet: http://wiki.postgresql.org/wiki/Slow_Counting For a question asked this frequently, it should probably be in the FAQ. I'll add a link from there to the more thorough write-up.

Re: [PERFORM] Slow Select count(*) ... query on table with 60 Mio. rows

Kevin Grittner wrote: Greg Smith g...@2ndquadrant.com wrote: The content was already there, just not linked into the main FAQ yet: http://wiki.postgresql.org/wiki/Slow_Counting For a question asked this frequently, it should probably be in the FAQ. I'll add a link from there to

Re: [PERFORM] Slow Select count(*) ... query on table with 60 Mio. rows

Greg Smith g...@2ndquadrant.com wrote: There's a whole list of FAQs that are documented on the wiki but not in the main FAQ yet leftover from before the main FAQ was hosted there. You can see them all at http://wiki.postgresql.org/wiki/Frequently_Asked_Questions I just haven't had time

Re: [PERFORM] Slow Select count(*) ... query on table with 60 Mio. rows

Kevin Grittner wrote: Greg Smith g...@2ndquadrant.com wrote: There's a whole list of FAQs that are documented on the wiki but not in the main FAQ yet leftover from before the main FAQ was hosted there. You can see them all at http://wiki.postgresql.org/wiki/Frequently_Asked_Questions I