On Tue, 2006-03-07 at 10:29, andremachado wrote:
> Hello,
> A friend asked for help to accelerate some postgresql queries on postgresql
> 8.1.2 for windows.
> He is comparing with firebird.
> Firebird was being up to 90 times faster at some queries.
> Attached is a gziped text file containing some steps I tried on a simple
> example query.
> Could get improvements from 270 seconds to 74 seconds.
> But Firebird effortlessly still can perform the same query at 20 seconds.
> Please, do you have some suggestion?
First off, PostgreSQL on Windows is still kinda new, so it's quite
possible that on some flavor of unix the disparity we're seeing wouldn't
be so great. You may be seeing some issue with PostgreSQL's fairly new
windows port instead of some basic postgresql problem.
Is this running on the same basic hardware for both databases? I would
imagine so, but just wanted to check.
As someone else mentioned, try cranking up work mem, and to a lesser
extent, shared_buffers.
Also, as mentioned, why are bitmap scans and seq scans turned off?
Bitmap scans are quite a nice improvement, and sometimes, a sequential
scan is faster than an index. Forcing PostgreSQL to always use an index
it not really a good idea.
Lastly, I noticed that after you clusters on all your indexes, the query
planner switched from a merge join to a hash join, and it was slower.
You might wanna try turning off hash joins for a quick test to see if
merge joins are any faster.
Lastly, you might want to compare the two databases running on linux or
BSD to see how they compare there.
