Re: postgresql is slow with larger table even it is in RAM

In response to "sathiya psql" <sathiya(dot)psql(at)gmail(dot)com>:
> >
> > Yes. It takes your hardware about 3 seconds to read through 700M of ram.
> >
> >
> > Keep in mind that you're not just reading RAM. You're pushing system
> > requests through the VFS layer of your operating system, which is treating
> > the RAM like a disk (with cylinder groups and inodes and blocks, etc) so
> > you have all that processing overhead as well. What filesystem did you
> > format the RAM disk with?
>
> tmpfs

I'm not an expert, but according to wikipedia:

"tmpfs (previously known as shmfs) distinguishes itself from the Linux ramdisk device by allocating memory dynamically and by allowing less-used pages to be moved onto swap space."

Both dynamically allocating and swapping are potential problems, but I
don't know how to tell you to determine if they're issues or not.

> > Why are you doing this? If you have enough RAM to store the table, why
> > not just allocate it to shared buffers?
>
> just allocating will make read from hdd to RAM at first time, to eliminate
> that

PostgreSQL is still going to copy the data from your RAM disk into shared
buffers before working with it, so you still have that overhead. All
you're escaping is the time involved in physical disk activity, which is
what shared_buffers are designed to avoid.

> are you saying it will take 3 seconds surely if i have 50 lakh record

No. That is dependent on your hardware and other factors.

You are trying to use the system in a non-standard configuration. If it
doesn't work that way, don't be surprised.

Also, what are you expectations? Honestly, I don't see any problems with
the results you're getting, they're about what I would expect. Are you
trying to compare PostgreSQL to MySQL/MyISAM? More directly, what is
your purpose in starting this email conversation? What are you hoping
to accomplish?

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023