Обсуждение: BUG #5733: Strange planer behaviour with inherited tables

The following bug has been logged online:

Bug reference:      5733
Logged by:          Marcus Wirsing
Email address:      mw@hesotech.de
PostgreSQL version: 9.0.1
Operating system:   Windows XP 32
Description:        Strange planer behaviour with inherited tables
Details:

when I execute the following script, the planer always makes a seq. scan
over all child tables.

drop schema if exists schema_0 cascade;
drop schema if exists schema_1 cascade;
drop schema if exists schema_2 cascade;
create schema schema_0;
create schema schema_1;
create schema schema_2;
CREATE TABLE schema_0.testtab
(
  entry bigserial NOT NULL,
  status integer,
  chnsetid integer,
  dt_package timestamp with time zone,
  dwell interval,
  cnt_mv integer,
  min_mv real[],
  PRIMARY KEY (entry)
);
create table schema_1.testtab (primary key(entry)) inherits
(schema_0.testtab);
create table schema_2.testtab (primary key(entry)) inherits
(schema_0.testtab);

EXPLAIN SELECT entry
  FROM schema_0.testtab
  where entry > 1000
  order by entry
  limit 1;



result:
"Limit  (cost=62.73..62.73 rows=1 width=8)"
"  ->  Sort  (cost=62.73..64.66 rows=771 width=8)"
"        Sort Key: schema_0.testtab.entry"
"        ->  Result  (cost=0.00..58.88 rows=771 width=8)"
"              ->  Append  (cost=0.00..58.88 rows=771 width=8)"
"                    ->  Seq Scan on testtab  (cost=0.00..19.63 rows=257
width=8)"
"                          Filter: (entry > 1000)"
"                    ->  Seq Scan on testtab  (cost=0.00..19.63 rows=257
width=8)"
"                          Filter: (entry > 1000)"
"                    ->  Seq Scan on testtab  (cost=0.00..19.63 rows=257
width=8)"
"                          Filter: (entry > 1000)"




when I remove the min_mv real[] the planer makes an index scan as expected.





"  ->  Sort  (cost=70.55..73.30 rows=1101 width=8)"
"        Sort Key: schema_0.testtab.entry"
"        ->  Result  (cost=7.09..65.05 rows=1101 width=8)"
"              ->  Append  (cost=7.09..65.05 rows=1101 width=8)"
"                    ->  Bitmap Heap Scan on testtab  (cost=7.09..21.68
rows=367 width=8)"
"                          Recheck Cond: (entry > 1000)"
"                          ->  Bitmap Index Scan on testtab_pkey
(cost=0.00..7.00 rows=367 width=0)"
"                                Index Cond: (entry > 1000)"
"                    ->  Bitmap Heap Scan on testtab  (cost=7.09..21.68
rows=367 width=8)"
"                          Recheck Cond: (entry > 1000)"
"                          ->  Bitmap Index Scan on testtab_pkey
(cost=0.00..7.00 rows=367 width=0)"
"                                Index Cond: (entry > 1000)"
"                    ->  Bitmap Heap Scan on testtab  (cost=7.09..21.68
rows=367 width=8)"
"                          Recheck Cond: (entry > 1000)"
"                          ->  Bitmap Index Scan on testtab_pkey
(cost=0.00..7.00 rows=367 width=0)"
"                                Index Cond: (entry > 1000)"

"Marcus Wirsing" <mw@hesotech.de> writes:
> when I execute the following script, the planer always makes a seq. scan
> over all child tables.
> when I remove the min_mv real[] the planer makes an index scan as expected.

I see no bug here.  Adding or removing a column changes the estimated
width of rows, hence the estimated row counts, and that makes some small
differences in the cost estimates.  For empty toy tables like these,
the cost estimates for different scan types are close enough together
that seemingly irrelevant details can change the outcome.

If you've got an actual problem, it's unlikely that discussing trivial
examples like this will help get to the bottom of it.  We'd need to look
at example tables that have realistic statistics.

            regards, tom lane