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)

Nino Arsov <nino(dot)arsov(at)gmail(dot)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