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)
In-reply-to: 
 <CAMGNKZ+5ipK+o5==gbpLrdgcTEtGVx68H1NFzJzQNVwer3XG+w@mail.gmail.com>
References: 
 <CAMGNKZ+5ipK+o5==gbpLrdgcTEtGVx68H1NFzJzQNVwer3XG+w@mail.gmail.com>
Comments: In-reply-to Nino Arsov <nino.arsov@gmail.com>
 message dated "Tue, 03 Feb 2015 12:35:27 +0100"
Date: Tue, 03 Feb 2015 10:35:24 -0500
Message-ID: <3456.1422977724@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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