From: Tom Lane <tgl@sss.pgh.pa.us>
To: Relyea, Mike <Mike.Relyea@xerox.com>
Cc: pgsql-performance@postgresql.org, "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Subject: Re: Help optimize view
Date: Mon, 13 Aug 2007 16:17:23 -0400
Message-ID: <21054.1187036243@sss.pgh.pa.us> (raw)
In-Reply-To: <1806D1F73FCB7F439F2C842EE0627B18065F78DF@USA0300MS01.na.xerox.net>
References: <1806D1F73FCB7F439F2C842EE0627B18065BED58@usa0300ms01.na.xerox.net>
<14336.1186782218@sss.pgh.pa.us>
<1806D1F73FCB7F439F2C842EE0627B18065BF2C0@USA0300MS01.na.xerox.net>
<46C03F8C.EE98.0025.0@wicourts.gov>
<1806D1F73FCB7F439F2C842EE0627B18065F78DF@USA0300MS01.na.xerox.net>
"Relyea, Mike" <Mike.Relyea@xerox.com> writes:
> I've increased shared_buffers to 128MB, and restarted the server. My
> total run time didn't really change.
It doesn't look like you can hope for much in terms of improving the
plan. The bulk of the time is going into scanning ParameterValues and
Measurements, but AFAICS there is no way for the query to pull fewer
rows from those tables than it is doing, and the size of the join means
that a nestloop indexscan is likely to suck. (You could try forcing one
by setting enable_hashjoin and enable_mergejoin to OFF, but I don't have
much hope for that.)
If you haven't played with work_mem yet, increasing that might make the
hash joins go a bit faster --- but it looks like most of the time is
going into the raw relation scans, so there's not going to be a lot of
win to be had there either.
Basically, joining lots of rows like this takes awhile. If you have to
have a faster answer, I can only suggest rethinking your table design.
Sometimes denormalization of the schema is necessary for performance.
regards, tom lane
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, Mike.Relyea@xerox.com, Kevin.Grittner@wicourts.gov
Subject: Re: Help optimize view
In-Reply-To: <21054.1187036243@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