Re: How to read query plan

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: John Arbash Meinel <john(at)arbash-meinel(dot)com>
Cc: Miroslav Šulc <miroslav(dot)sulc(at)startnet(dot)cz>, PGSQL mailing list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: How to read query plan
Date: 2005-03-13 19:15:54
Message-ID: 18381.1110741354@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

John Arbash Meinel <john(at)arbash-meinel(dot)com> writes:
> How about a quick side track.
> Have you played around with your shared_buffers, maintenance_work_mem,
> and work_mem settings?

Indeed. The hash joins seem unreasonably slow considering how little
data they are processing (unless this is being run on some ancient
toaster...). One thought that comes to mind is that work_mem may be
set so small that the hashes are forced into multiple batches.

Another question worth asking is what are the data types of the columns
being joined on. If they are character types, what locale and encoding
is the database using?

> Are you re-running the query multiple times, and reporting the later
> speeds, or just the first time? (If nothing is loaded into memory, the
> first run is easily 10x slower than later ones.)

That cost would be paid during the bottom-level scans though. The thing
that strikes me here is that nearly all of the cost is being spent
joining.

> What version of postgres are you using?

And what's the platform (hardware and OS)?

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2005-03-13 19:22:31 Re: Bumping libpq version number?
Previous Message Miroslav Šulc 2005-03-13 19:07:00 Re: How to read query plan

Browse pgsql-performance by date

  From Date Subject
Next Message John Arbash Meinel 2005-03-13 19:26:29 Re: How to read query plan
Previous Message Miroslav Šulc 2005-03-13 19:07:00 Re: How to read query plan