BUG #5733: Strange planer behaviour with inherited tables

From: "Marcus Wirsing" <mw(at)hesotech(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5733: Strange planer behaviour with inherited tables
Date: 2010-10-30 11:07:28
Message-ID: 201010301107.o9UB7Sa8051131@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 5733
Logged by: Marcus Wirsing
Email address: mw(at)hesotech(dot)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)"

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Mark Stosberg 2010-10-30 14:04:11 BUG #5734: autovacuum_enabled input should be validated, standardized.
Previous Message Robert Haas 2010-10-30 00:24:54 Re: [PERFORM] typoed column name, but postgres didn't grump