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

Отсутствует оптимизация на patition таблицах

From: Maxim Vetrov <muxas(at)mail(dot)ru>
To: pgsql-ru-general(at)postgresql(dot)org
Subject: Отсутствует оптимизация на patition таблицах
Date: 2007-12-24 12:48:11
Message-ID: 476FAA8B.4080506@mail.ru (view raw or flat)
Thread:
Lists: pgsql-ru-general
Доброго времени суток!

FreeBSD devel.muxas.net 6.2-RELEASE-p8 FreeBSD 6.2-RELEASE-p8 #0: Sun 
Oct 21 19:39:17 VLAST 2007     
root(at)devel(dot)muxas(dot)net:/usr/obj/usr/src/sys/DEVEL  i386
postgres (PostgreSQL) 8.2.5

Никак не получается подружить оптимизатор запросов с partitioning. Вот 
живой пример:

stac=# show constraint_exclusion;
 constraint_exclusion
----------------------
 on
(1 запись)

stac=# create table parent(col1 smallint);
CREATE TABLE
stac=# create table child1(check (col1=1)) inherits(parent);
CREATE TABLE
stac=# create table child2(check (col1=2)) inherits(parent);
CREATE TABLE
stac=# insert into child1 values(1);
INSERT 0 1
stac=# insert into child2 values(2);
INSERT 0 1
stac=# explain select * from parent where col1=1;
                                QUERY PLAN                                
---------------------------------------------------------------------------
 Result  (cost=0.00..114.75 rows=33 width=2)
   ->  Append  (cost=0.00..114.75 rows=33 width=2)
         ->  Seq Scan on parent  (cost=0.00..38.25 rows=11 width=2)
               Filter: (col1 = 1)
         ->  Seq Scan on child1 parent  (cost=0.00..38.25 rows=11 width=2)
               Filter: (col1 = 1)
         ->  Seq Scan on child2 parent  (cost=0.00..38.25 rows=11 width=2)
               Filter: (col1 = 1)
(8 rows)

stac=# analyze verbose parent;
INFO:  analyzing "public.parent"
INFO:  "parent": scanned 0 of 0 pages, containing 0 live rows and 0 dead 
rows; 0 rows in sample, 0 estimated total rows
ANALYZE
stac=# analyze verbose child1;
INFO:  analyzing "public.child1"
INFO:  "child1": scanned 1 of 1 pages, containing 1 live rows and 0 dead 
rows; 1 rows in sample, 1 estimated total rows
ANALYZE
stac=# analyze verbose child2;
INFO:  analyzing "public.child2"
INFO:  "child2": scanned 1 of 1 pages, containing 1 live rows and 0 dead 
rows; 1 rows in sample, 1 estimated total rows
ANALYZE
stac=# explain select * from parent where col1=1;
                               QUERY PLAN                               
-------------------------------------------------------------------------
 Result  (cost=0.00..40.28 rows=13 width=2)
   ->  Append  (cost=0.00..40.28 rows=13 width=2)
         ->  Seq Scan on parent  (cost=0.00..38.25 rows=11 width=2)
               Filter: (col1 = 1)
         ->  Seq Scan on child1 parent  (cost=0.00..1.01 rows=1 width=2)
               Filter: (col1 = 1)
         ->  Seq Scan on child2 parent  (cost=0.00..1.01 rows=1 width=2)
               Filter: (col1 = 1)
(8 rows)

Это конечно самый простой тест, но абсолютно то же самое происходит и с 
большими таблицами по 500,000 записей. Vacuum analyze не помогают; 
constraint_exclusion включен. В чем загвоздка?

muxas

Responses

pgsql-ru-general by date

Next:From: Ivan ZolotukhinDate: 2007-12-25 10:02:54
Subject: Re: [pgsql-ru-general] Отсутствует оптимизация на patition таблицах
Previous:From: silly_sadDate: 2007-11-28 07:18:48
Subject: log rotation

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