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.
