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)"