From: Richard Huxton <dev@archonet.com>
To: Casey Allen Shobe <casey@shobe.info>
Cc: pgsql-performance@postgresql.org
Subject: Re: Fighting the planner >:-(
Date: Fri, 01 Feb 2013 18:50:13 +0000
Message-ID: <510C0E65.7060308@archonet.com> (raw)
In-Reply-To: <CAFmVg3gLE9QZRN0pPZdaL6+=1tYC0nPWE7DjPkvYQ2gcnDyQRQ@mail.gmail.com>
References: <CAFmVg3jE_HymBX558M5+Hh3iaUm7OAj_FVk98SGuGk8B3sWuvA@mail.gmail.com>
<CAFmVg3gLE9QZRN0pPZdaL6+=1tYC0nPWE7DjPkvYQ2gcnDyQRQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 01/02/13 17:54, Casey Allen Shobe wrote:
> My apologies - I included the wrong version of the query
> before...during testing I had tried deparameterizing a few of the
> input parameters. I also accidentally left out the schema for the
> network_config_tot2 table from the initial paste.
>
> Here is an updated paste, which shows the correct query in a prepare
> statements. The explain plans are from explain execute hewitt_test (...):
> http://pgsql.privatepaste.com/00c582c840
>
> Here is the correct explain plan for this statement (still bad):
> http://explain.depesz.com/s/c46
Three quick observations before the weekend.
1. You said config_id was now "smallint" in your email, but it reads
"int" in the pastes above.
Doesn't matter much which, but just checking we've got the right pastes.
2. The total estimated cost of both queries is about the same
(477,225.19 for the varchar, 447,623.86 for the int).
This suggests something about your configuration doesn't match the
performance of your machine, since presumably the int version is taking
at least twice as long as the varchar one.
3. Interestingly, the config_id search on both plans seems to be using a
Bitmap Index, so I'm not sure that's the root cause. However, the
varchar version seems to have a literal string it's matching against. If
you've manually substituted in a literal value, that could be skewing
the tests.
And two things for you to try if you would:
1. Can you just check and see if any of the row estimates are horribly
off for any particular clause in the query?
2. You mention your config settings are mostly at default. What's your
work_mem and can you increase it? You can issue a SET for the current
session, no need to change it globally. If you've got the RAM try
doubling it, then double it again. See what happens to your plan then.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: dev@archonet.com, casey@shobe.info
Subject: Re: Fighting the planner >:-(
In-Reply-To: <510C0E65.7060308@archonet.com>
* 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