[GENERAL] postgresql is slow with larger table even it is in RAM
I have a table with 32 lakh record in it. Table size is nearly 700 MB, and my machine had a 1 GB + 256 MB RAM, i had created the table space in RAM, and then created this table in this RAM. So now everything is in RAM, if i do a count(*) on this table it returns 327600 in 3 seconds, why it is taking 3 seconds ? because am sure that no Disk I/O is happening. ( using vmstat i had confirmed, no disk I/O is happening, swap is also not used ) Any Idea on this ??? I searched a lot in newsgroups ... can't find relevant things ( because everywhere they are speaking about disk access speed, here i don't want to worry about disk access ) If required i will give more information on this.
Re: [GENERAL] postgresql is slow with larger table even it is in RAM
On 2008-03-25 09:51, sathiya psql wrote: Table size is nearly 700 MB, and my machine had a 1 GB + 256 MB RAM, i had created the table space in RAM, and then created this table in this RAM. So now everything is in RAM, if i do a count(*) on this table it returns 327600 in 3 seconds, why it is taking 3 seconds ? PostgreSQL needs to scan the whole table. And 700MB is a lot of data even when in RAM. Check this: $ dd if=/dev/zero bs=1M count=700 | cat /dev/null 734003200 bytes (734 MB) copied, 1.38732 s, 529 MB/s This command will just copy 700MB of zeros around in RAM, and it takes over 1s to do it on my Pentium D 3,4GHz. PostgreSQL would need to do much more with this data. Maybe you just got bitten by count(*) gotcha: http://sql-info.de/en/postgresql/postgres-gotchas.html#1_7 Maybe you'll get by with an estimate. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql is slow with larger table even it is in RAM
Please stop reposting your questions to multiple groups. Since all your questions are about performance, please stick to the pgsql-performance list. Posting to pgsql-sql is not really appropriate, and in pgsql-admin you're totally off-topic. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql is slow with larger table even it is in RAM
ok On Tue, Mar 25, 2008 at 5:33 PM, Alvaro Herrera [EMAIL PROTECTED] wrote: Please stop reposting your questions to multiple groups. Since all your questions are about performance, please stick to the pgsql-performance list. Posting to pgsql-sql is not really appropriate, and in pgsql-admin you're totally off-topic. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
