Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id BF5242E3817 for ; Mon, 24 Dec 2007 08:48:19 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 99566-02 for ; Mon, 24 Dec 2007 08:48:15 -0400 (AST) Received: from mx27.mail.ru (mx27.mail.ru [194.67.23.23]) by postgresql.org (Postfix) with ESMTP id B8E842E3816 for ; Mon, 24 Dec 2007 08:48:15 -0400 (AST) Received: from [80.243.71.203] (port=9103 helo=devel.muxas.net) by mx27.mail.ru with asmtp id 1J6mj7-000B4z-00 for pgsql-ru-general@postgresql.org; Mon, 24 Dec 2007 15:48:13 +0300 Message-ID: <476FAA8B.4080506@mail.ru> Date: Mon, 24 Dec 2007 22:48:11 +1000 From: Maxim Vetrov User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; ru-RU; rv:1.8.1.9) Gecko/20071202 SeaMonkey/1.1.6 MIME-Version: 1.0 To: pgsql-ru-general@postgresql.org Subject: =?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?= Content-Type: text/plain; charset=KOI8-R; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.423 tagged_above=0 required=5 tests=AWL=1.266, BAYES_50=0.001, SUBJECT_FUZZY_TION=0.156 X-Spam-Level: * X-Archive-Number: 200712/1 X-Sequence-Number: 771 Доброго времени суток! 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