Re: Query performance

On Mon, 12 Oct 2009, S Arvind wrote:
> I can understand left join, actually can any one tell me why sort operation is carried
> out and wat Materialize means...
> Can anyone explain me the mentioned plan with reason(s)?

> Merge Left Join  (cost=62451.86..67379.08 rows=286789 width=0)
>   Merge Cond: (a.id = b.id)
>   ->  Sort  (cost=18610.57..18923.27 rows=125077 width=8)
>         Sort Key: a.id
>         ->  Seq Scan on a  (cost=0.00..6309.77 rows=125077 width=8)
>   ->  Materialize  (cost=43841.28..47426.15 rows=286789 width=8)
>         ->  Sort  (cost=43841.28..44558.26 rows=286789 width=8)
>             Sort Key: b.id
>            ->  Seq Scan on b (cost=0.00..13920.89 rows=286789 width=8)

This is a merge join. A merge join joins together two streams of data,
where both streams are sorted, by placing the two streams side by side and
advancing through both streams finding matching rows. The algorithm can
use a pointer to a position in both of the streams, and advance the
pointer of the stream that has the earlier value according to the sort
order, and therefore get all the matches.

You are performing a query over the whole of both of the tables, so the
cheapest way to obtain a sorted stream of data is to do a full sequential
scan of the whole table, bring it into memory, and sort it. An alternative
would be to follow a B-tree index if one was available on the correct
column, but that is usually more expensive unless the table is clustered
on the index or only a small portion of the table is to be read. If you
had put a "LIMIT 10" clause on the end of the query and had such an index,
it would probably switch to that strategy instead.

The materialise step is effectively a buffer that allows one of the
streams to be rewound cheaply, which will be necessary if there are
multiple rows with the same value.

Does that answer your question?

Matthew

--
The only secure computer is one that's unplugged, locked in a safe,
and buried 20 feet under the ground in a secret location...and i'm not
even too sure about that one. --Dennis Huges, FBI