Querying data faster in PostgreSQL

Anon

Hello,

My friend and I were arguing about different way to make postgresql run faster. We have argued about this several times and we can not reach an agreeable solution.

I would appreciate it if you guys can weigh in with your thoughts.

Assuming that the query is a simple select query running on a large table with over a million records. The table would probably indexed several times, and tere is no calculation needed in this query.

If the machine that postgresql is running on is a SMP machine with around a couple of gigs of ram, and the query runs slowly. What would make the query run faster?

I believe that adding a faster hard drive such as scsi 160's on raid 0 or 1 would be a better solution. He believes that adding more ram would be a better solution. In my opinion if we have two hard drives acessing records in a database it will be fater than one.

What do you guys thing? Are we both wrong, what would make this query run faster?

Your answers based on practical exprience is welcomed. I appreciate answers based in theory, but as I have found out in my few years in the comptuer buisness, theory rarely translates well to the real world.

What do you guys thing? Are we both wrong, what would make this query run faster?

bfranco

well, it's hard to say... there are many variables that are not specified. I mean, for a SELECT FROM {my_huge_table} (without condition) you will need a fast disk an lots of ram... so, I'll tell you what I think for a generic* case:

I think that if you have enough memory to store all the pages of the indexes you are using for your query loaded into ram, it will make the diference.
Of course, first time you run your query it'll be very slow, but as pages are cached into main memory it will go much faster.

It doesn't matter how fast your hard drive works, cause we are talking about milliseconds here, meanwile ram timings are measured in nanoseconds.

I think your friend is right, but it's just my theorical opinion 😛

[deleted]

I think neither is right at the moment.

Before I go on: how slow is slow, and how much CPUtime can the postgres process use (or: how heavy is the load on that server?)

The first thing you should look at is the query itself.
you say "The table would probably indexed several times"
but are there any usefull indexes, and are you sure you don't have too many indexes?

Second, is postgres configured to use all that RAM you have? it's no good to have 4 gigs of ram and tell postgres to use only 4megs max.

Third, is your kernel configured to allow postgres to use a lot of ram in shared memory?

BTW Francois: seektimes are in ms, but transfertimes are just as important, upgrading from 50MB/s to 100MB/s will give a clear difference in performance.

Anon

Yes the query has useful indexes. An the linux machine will use all the ram. Slow would be longer than an hour. The kernel and everything else is optimized.

[deleted]

What does TOP tell you while the query is running? Is there a lot of swap in use?

bfranco

vincent, you're a pain in the ass.

we assume that you have enough ram, all cpu time is available for postgresql, the database is optimized to use all available memory, the computer is turned on, there are 110 V on the power outlet, and we have paid the electric company bill.

just tell us what you think.

[deleted]

"vincent, you're a pain in the ass."

ow.. high IQ response there Frank.

"we assume that you have enough ram, all cpu time is available for postgresql, the database is optimized to use all available memory, the computer is turned on, "

Assumption is the mother of all fuckups.

"there are 110 V on the power outlet,"
which would be 120 volts too low for 230Volt machines, thats fuckup number one.

I like to get all the fracts about a problem before I start giving solutions.

If I had a penny for every time someone complained about a slow query and it turned out they had no indexes or they tried to run 500 concurrent queries with only 64MB of RAM.

"just tell us what you think."

That is what I was trying to do, but apperently you can tell what's wrong with a setup without knowing anything about it, so I'll save myself some trouble and leave it to you.

Anon

Top would say something like

85% On processor 1
50% On processor 2

We would use all the physcial ram, and approx. 200 MB of 400 MB swap.

BTW this system is not running on SCSI, it would use EIDE. I didn't explictly state that in my first post, although SCSI could be a solution.

Thank you

Anon

As far as indexes go:

The select query also uses a where clause. The where clause is based on the field1, and field 2.

There is a seperate index on field1, and on field2, there is also an index which includes
filed1 and field2.

ex:

CREATE INDEX 1 ON table1 (field1);
CREATE INDEX 2 ON table1 (field2);
CREATE INDEX 3 ON table1 (field1, field2)

bfranco

jajaja, you make me laugh.