Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group