pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: John Arbash Meinel <john@arbash-meinel.com>
Cc: Miroslav Ć ulc <miroslav.sulc@startnet.cz>
Cc: PGSQL mailing list <pgsql-performance@postgresql.org>
Subject: Re: How to read query plan
Date: Sun, 13 Mar 2005 14:15:54 -0500
Message-ID: <18381.1110741354@sss.pgh.pa.us> (raw)
In-Reply-To: <423478B1.7080106@arbash-meinel.com>
References: <42345D24.8070104@startnet.cz>
	<4234692E.3050802@arbash-meinel.com>
	<42347407.2000202@startnet.cz>
	<423478B1.7080106@arbash-meinel.com>

John Arbash Meinel <john@arbash-meinel.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



view thread (37+ messages)  latest in thread

Message-ID: <18381.1110741354@sss.pgh.pa.us>
Permalink:  ../18381.1110741354@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/18381.1110741354@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, john@arbash-meinel.com, miroslav.sulc@startnet.cz
  Subject: Re: How to read query plan
  In-Reply-To: <18381.1110741354@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox