From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nino Arsov <nino.arsov@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Estimating the execution cost of a query in a partitioned schema: Weird execution plans and totally wrong execution costs (pg_class and pg_statistic)
Date: Tue, 03 Feb 2015 10:35:24 -0500
Message-ID: <3456.1422977724@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMGNKZ+5ipK+o5==gbpLrdgcTEtGVx68H1NFzJzQNVwer3XG+w@mail.gmail.com>
References: <CAMGNKZ+5ipK+o5==gbpLrdgcTEtGVx68H1NFzJzQNVwer3XG+w@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Nino Arsov <nino.arsov@gmail.com> writes:
> Using EXPLAIN we have successfully managed to get very similar execution
> plans and costs by the planner as if there were data in the newly created
> partitions. This method works just fine as long as the queries to be
> estimated consist of a join of no more than 2 tables.
> When we try to estimate a query's cost that contains a join between 3 or
> more tables we get huge costs and wrong plans (in the rank of millions of
> cost units). When data is actually loaded into all of the partitions, the
> cost does not exceed a few thousand cost units.
Have you modified the planner at all? Because it's a bit hard to see how
inserting faked-up statistics as described would work properly in a 2-way
join but not in a 3-way join. A bug in some hack or other would be a
much more plausible explanation.
I'm also curious about exactly how you're inserting new data into
pg_statistic --- the "anyarray" columns that are used there are not
readily modifiable from SQL.
(This is pretty off-topic for pgsql-admin, btw.)
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
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-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, nino.arsov@gmail.com
Subject: Re: Estimating the execution cost of a query in a partitioned schema: Weird execution plans and totally wrong execution costs (pg_class and pg_statistic)
In-Reply-To: <3456.1422977724@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