agora inbox for pgsql-ru-general@postgresql.org
help / color / mirror / Atom feedFrom: Maxim Vetrov <muxas@mail.ru>
To: pgsql-ru-general@postgresql.org
Subject: Отсутствует оптимизация на patition таблицах
Date: Mon, 24 Dec 2007 22:48:11 +1000
Message-ID: <476FAA8B.4080506@mail.ru> (raw)
Доброго времени суток!
FreeBSD devel.muxas.net 6.2-RELEASE-p8 FreeBSD 6.2-RELEASE-p8 #0: Sun
Oct 21 19:39:17 VLAST 2007
root@devel.muxas.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
Message-ID: <476FAA8B.4080506@mail.ru>
Permalink: ../476FAA8B.4080506@mail.ru/
Also on: postgresql.org/message-id/476FAA8B.4080506@mail.ru
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-ru-general@postgresql.org
Cc: muxas@mail.ru
Subject: Re: =?KOI8-R?Q?=EF=D4=D3=D5=D4=D3=D4=D7=D5=C5=D4_=CF=D0=D4=C9=CD=C9?= =?KOI8-R?Q?=DA=C1=C3=C9=D1_=CE=C1_patition_=D4=C1=C2=CC=C9=C3=C1=C8?=
In-Reply-To: <476FAA8B.4080506@mail.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox