Re: Query performance

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 ...