Re: How to read query plan

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
=:->