Обсуждение: Отсутствует оптимизация на patition таблицах
Доброго времени суток!
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
Добрый день, Да, пример проверил, довольно странно, что не работает. Причину пока не знаю. Попробуйте написать в pg-general, там должны помочь. При этом лично я вполне успешно и давно использую constraint_exclusion с единственным отличием, что check constraint у меня between, а не точное равенство. Regards, Ivan On Dec 24, 2007 3:48 PM, Maxim Vetrov <muxas@mail.ru> wrote: > Доброго времени суток! > > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
Ivan Zolotukhin wrote: > Добрый день, > > Да, пример проверил, довольно странно, что не работает. Причину пока > не знаю. Попробуйте написать в pg-general, там должны помочь. > > При этом лично я вполне успешно и давно использую constraint_exclusion > с единственным отличием, что check constraint у меня between, а не > точное равенство. > > Regards, > Ivan > > On Dec 24, 2007 3:48 PM, Maxim Vetrov <muxas@mail.ru> wrote: > >> Доброго времени суток! >> >> 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 >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq >> >> > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > Я, кажется, нашел ответ, но нельзя сказать, что он меня устраивает. При изменении типа поля на integer, оптимизация включается, т.е. по-видимому кол-во значений smallint достаточно мало,чтобы оптимизатор считал нужным использовать обычный просмотр значений по порядку... muxas