Обсуждение: postgresql is slow with larger table even it is in RAM

Dear Friends,
     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.

On Tuesday 25 March 2008, sathiya psql wrote:
> Dear Friends,
>      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.


I can't really say anything about your RAM issue, but count(*) is always a bad
idea if it can be avoided. Assuming you have a primary key on the table, do a
count(pk). In my experience that alone cuts down up to 30% of execution time.

    Uwe

yes, i have both the index and primary key on a column "id",
and am doing the

SELECT count(id) from TABLE;

this is taking 3 seconds

some more info....

/proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 15
model           : 4
model name      : Intel(R) Celeron(R) CPU 2.66GHz
stepping        : 1
cpu MHz         : 2667.031
cache size      : 256 KB
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 5
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc up pni monitor ds_cpl tm2 cid cx16 xtpr
bogomips        : 5340.26


Is there any specifications, a table should not contain more than N number of records......

This count(id) am doing for experimentations, not only this query all the queries are taking much time...

Any idea on this ??? how to make queries to execute faster when the number of rows are nearly 50 lakh

Can you provide the output of

explain analyze select count(id) from table;

?

Uwe




On Tuesday 25 March 2008, sathiya psql wrote:
> On Tue, Mar 25, 2008 at 2:11 PM, CZUCZY Gergely
> <gergely.czuczy@harmless.hu>
>
> wrote:
> > May I ask, do you have any indeces or a primary key on that table?
>
> yes, i have both the index and primary key on a column "id",
> and am doing the
>
> SELECT count(id) from TABLE;
>
> this is taking 3 seconds

May I ask, do you have any indeces or a primary key on that table?

On Tue, 25 Mar 2008 14:06:25 +0530
"sathiya psql" <sathiya.psql@gmail.com> wrote:

> Dear Friends,
>      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.


--
Üdvölettel,

Czuczy Gergely
Harmless Digital Bt
mailto: gergely.czuczy@harmless.hu
Tel: +36-30-9702963

We could notice your processor is an Intel Celeron. They have a small cache, as you can see for yourself in your output.

But, what about postgresql.conf ? How many memory resources are being assigned in your configuration?

If you only have 1.25 GB of RAM and you're creating a tablespace in RAM which holds a table about 0.8 GB in size, and have created indexes in the same tablespace in RAM, you could be getting out of RAM. vmstat says you're not using swap, but if you leave the postgresql process only ~100 MB of RAM, it surely will have a bad performance.

Once the server is up and running, which is the output of free ?

Which tool are you using to retrieve data ? psql is pretty much faster than phpPgAdmin and this one is faster than pgAdmin.

Elvis E. Henríquez A.