Miroslav Šulc wrote:
Hi all,
I am new to PostgreSQL and query optimizations. We have recently moved our project from MySQL to PostgreSQL and we are having performance problem with one of our most often used queries. On MySQL the speed was sufficient but PostgreSQL chooses time expensive query plan. I would like to optimize it somehow but the query plan from EXPLAIN ANALYZE is little bit cryptic to me.
So the first thing I would like is to understand the query plan. I have read "performance tips" and FAQ but it didn't move me too much further.
I would appreciate if someone could help me to understand the query plan and what are the possible general options I can test. I think at this moment the most expensive part is the "Sort". Am I right? If so, how could I generally avoid it (turning something on or off, using parentheses for JOINs etc.) to force some more efficient query plan?
Thank you for any suggestions.
You really need to post the original query, so we can see *why* postgres thinks it needs to run the plan this way.
Also, the final sort actually isn't that expensive.
When you have the numbers (cost=xxx..yyy) the xxx is the time when the step can start, and the yyy is the time when the step can finish. For a lot of steps, it can start running while the sub-steps are still feeding back more data, for others, it has to wait for the sub-steps to finish.
The first thing to look for, is to make sure the estimated number of rows is close to the actual number of rows. If they are off, then postgres may be mis-estimating the optimal plan. (If postgres thinks it is going to only need 10 rows, it may use an index scan, but when 1000 rows are returned, a seq scan might have been faster.)
You seem to be doing a lot of outer joins. Is that necessary? I don't really know what you are looking for, but you are joining against enough tables, that I think this query is always going to be slow.
From what I can tell, you have 1 table which has 6364 rows, and you are grabbing all of those rows, and then outer joining it with about 11 other tables.
I would actually guess that the most expensive parts of the plan are the NESTED LOOPS which when they go to materialize have to do a sequential scan, and they get executed 6364 times. It looks like the other tables are small (only 3-5 rows), so it takes about 0.05 ms for each seqscan, the problem is that because you are doing it 6k times, it ends up taking about 300ms of your time.
You could try setting "set enable_nestloop to off". I don't know that it will be faster, but it could be.
In general, though, it seems like you should be asking a different question, rather than trying to optimize the query that you have.
Can you post the original SQL statement, and maybe describe what you are trying to do?
John =:->
signature.asc
Description: OpenPGP digital signature
