Wrong estimation of unique values of a table when using partitions [PostgreSQL 9.3.1]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Dear PostgreSQL developers,

I'm not sure if this is actually a bug or just a possible enhancement
to PostgreSQL.

In the example I've attached there are two child tables which inherit
from a common parent table. The parent table has an integer column
"v". Both are filled with random values, the first child with values
from 0-500 and the second one with 1000-1499. There is a second column
"p" that defines an exclusive partition of the children, thus each
child table is uniquely defined by the value of the p column.

When you query the distinct values for "v" for the first child table
directly the query planner guesses that there will be 500 rows (which
is already the precise answer). If you execute the same query on the
parent table instead, but restrict the "p" value to "1" (so that
PostgreSQL will use constraint exclusion and only scans the empty
parent table and the first child table) it will assume that there will
be 1000 different values in the result. Since constraint exclusion
already figured out that only one child table will be used and that
the parent table has no rows it could correctly guess the unique
number of values for the child table (and thus the complete query) as
well.

I run into this problem since we are using partitioned tables in our
production database with many different child tables and some queries
did not succeed to execute in a certain time limit in production. They
worked well on the development machines, since only a few child tables
exist there and the planner made better join selection choices on the
development installations.

Best,

Thomas

- --
Thomas Krause
Mitarbeiter Korpuslinguistik und Morphologie
Humboldt-Universität zu Berlin

Telefon: +49 (0)30 2093 9720
Raum 3.408, Dorotheenstraße 24, 10117 Berlin - Mitte
http://u.hu-berlin.de/korpling-thomaskrause
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.14 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJSgkg1AAoJEP4mVMq7eD64OhoH/1QDGoiVDEWLPxFBSoH3ZjhA
yfG5yTSfHoNwXWgDp2JU691UdgEY305cCs84rJ5OundF1sgLMs6+l/MCcdPBxrbZ
E5i49zT+j69Hl/ApSvRnHjBl6UKWLuE795zhbn3b4XfTHNJ51Y4as3FkpTMcTpsP
kmmC9lBYjraGhGxeU2hEYT3DLOuZb9LhbTwSl4T7yVuggPipsKlKcmDQi74uQSRx
mv/XJtHmoanx9rCdSeuHpYOn0MWl1wAdG3cD1D9PvcMtDkYGimKcomvhdN8RX/F6
YMRWSQ2+mF2iTliJdeVuukNqaAjM+wBXisgd0icsjKqvulpQAMG54RoA8rFSYmg=
=bLyO
-----END PGP SIGNATURE-----