Thread: Query performance

In the below query both table has less than 1 million data. Can u tell me the reason of this plan?
why its takin extensive cost , seq scan and sorting?? wat is Materialize?

select 1 from  service_detail
left join non_service_detail on non_service_detail_service_id = service_detail.service_detail_id



Merge Left Join  (cost=62451.86..67379.08 rows=286789 width=0)
  Merge Cond: (service_detail.service_detail_id = non_service_detail.non_service_detail_service_id)
  ->  Sort  (cost=18610.57..18923.27 rows=125077 width=8)
        Sort Key: service_detail.service_detail_id
        ->  Seq Scan on service_detail  (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: non_service_detail.non_service_detail_service_id
              ->  Seq Scan on non_service_detail  (cost=0.00..13920.89 rows=286789 width=8)

On Mon, Oct 12, 2009 at 12:21 PM, S Arvind <arvindwill@gmail.com> wrote:

In the below query both table has less than 1 million data. Can u tell me the reason of this plan?
why its takin extensive cost , seq scan and sorting?? wat is Materialize?

select 1 from  service_detail
left join non_service_detail on non_service_detail_service_id = service_detail.service_detail_id



Merge Left Join  (cost=62451.86..67379.08 rows=286789 width=0)
  Merge Cond: (service_detail.service_detail_id = non_service_detail.non_service_detail_service_id)
  ->  Sort  (cost=18610.57..18923.27 rows=125077 width=8)
        Sort Key: service_detail.service_detail_id
        ->  Seq Scan on service_detail  (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: non_service_detail.non_service_detail_service_id
              ->  Seq Scan on non_service_detail  (cost=0.00..13920.89 rows=286789 width=8)

A) it is a left join, meaning - everything is pulled from left side,
B) there are no conditions, so ... ... everything is pulled again from left side.
 

--
GJ

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

This is an EXPLAIN, not an EXPLAIN ANALYSE. If it was an EXPLAIN ANALYSE, it would show how much memory was used, and whether it was a disc sort or an in-memory sort. As it is only an EXPLAIN, the query hasn't actually been run, and we have no information about whether the sort would be performed on disc or not.

On Mon, 12 Oct 2009, Grzegorz Jaśkiewicz wrote:

try setting work_mem to higher value. As postgresql will fallback to disc sorting if the
content doesn't fit in work_mem, which it probably doesn't (8.4+ show the memory usage
for sorting, which your explain doesn't have).

For reference, here's the EXPLAIN:

 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 an EXPLAIN, not an EXPLAIN ANALYSE. If it was an EXPLAIN ANALYSE, it would show how much memory was used, and whether it was a disc sort or an in-memory sort. As it is only an EXPLAIN, the query hasn't actually been run, and we have no information about whether the sort would be performed on disc or not.

Matthew

--
Hi! You have reached 555-0129. None of us are here to answer the phone and the cat doesn't have opposing thumbs, so his messages are illegible. Please leave your name and message after the beep ...

btw, what's the version of db ?
what's the work_mem setting ?

try setting work_mem to higher value. As postgresql will fallback to disc sorting if the content doesn't fit in work_mem, which it probably doesn't (8.4+ show the memory usage for sorting, which your explain doesn't have).

Thanks Grzegorz,
        But work memory is for each process (connection) rt? so if i keep more then 10MB will not affect the overall performance ?