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