X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7864DD7F54 for ; Sun, 28 Aug 2005 17:41:34 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 84281-02 for ; Sun, 28 Aug 2005 20:41:28 +0000 (GMT) Received: from Apachihuilliztli.mtu.ru (apachihuilliztli.mtu.ru [195.34.32.124]) by svr1.postgresql.org (Postfix) with ESMTP id 40413D7E3D for ; Sun, 28 Aug 2005 17:41:26 -0300 (ADT) Received: from umail.ru (umail.mtu.ru [195.34.32.101]) by Apachihuilliztli.mtu.ru (Postfix) with ESMTP id 8A08C20A6F6 for ; Mon, 29 Aug 2005 00:41:25 +0400 (MSD) (envelope-from ilia@obnovlenie.ru) Received: from [85.140.125.21] (HELO ilia) by umail.ru (CommuniGate Pro SMTP 4.2b6) with ESMTP id 558490720 for pgsql-performance@postgresql.org; Mon, 29 Aug 2005 00:41:24 +0400 From: "Ilia Kantor" To: Subject: intarray is broken ? (8.1b1) Date: Mon, 29 Aug 2005 00:41:18 +0400 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0017_01C5AC32.5E9A9B20" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Thread-Index: AcWsENc/4YuF/s7KSymYIzUiy/y9EA== Message-ID: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.192 required=5 tests=[AWL=0.141, FORGED_RCVD_HELO=0.05, HTML_MESSAGE=0.001] X-Spam-Level: X-Archive-Number: 200508/487 X-Sequence-Number: 14234 This is a multi-part message in MIME format. ------=_NextPart_000_0017_01C5AC32.5E9A9B20 Content-Type: text/plain; charset="koi8-r" Content-Transfer-Encoding: 7bit I tried to use intarray on 8.1 . It seems to give same estimates for anything I ask: explain analyze select * from objects_hier where tg && array[10001] explain analyze select * from objects_hier where tg && array[0] explain analyze select * from objects_hier where tg @ array[10001] explain analyze select * from objects_hier where tg ~ array[0] Some of queries cover whole table, some cover none, but all give same estimated number of rows: Bitmap Heap Scan on objects_hier (cost=2.10..102.75 rows=30 width=337) (actual time=0.028..0.028 rows=0 loops=1) Recheck Cond: (tg && '{0}'::integer[]) -> Bitmap Index Scan on gistbla2 (cost=0.00..2.10 rows= !! 30 !! width=0) (actual time=0.024..0.024 rows=0 loops=1) Index Cond: (tg && '{0}'::integer[]) See the number of estimated rows is 30 is all cases. But actually it varies from whole table (30000 rows) to 0. Looks like GIST indexes for intarray give no statistic at all. It makes them much much less useless than they could be.. Because planner can't plan them well and makes horrible mistakes. For example, puts nested loops in order when for each of 30k rows it makes an index scan within 5 rows => that leads to 30k nested scans, while it should for each of 5 rows perform single index scan among those 30k. Yes, I have all necessary indexes on tables. And yes, I run VACUUM FULL ANALYZE just before the tests. The lack of estimation is not documented anywhere so I just hope this is a bug and can be fixed fast :-) ------=_NextPart_000_0017_01C5AC32.5E9A9B20 Content-Type: text/html; charset="koi8-r" Content-Transfer-Encoding: quoted-printable

I tried to use intarray on 8.1 . It seems to = give same estimates for anything I ask:

explain analyze select * from objects_hier = where tg && array[10001]

explain analyze select * from objects_hier = where tg && array[0]

explain analyze select * from objects_hier = where tg @ array[10001]

explain analyze select * from objects_hier = where tg ~ array[0]

Some of queries cover whole table, some cover = none, but all give same estimated number of rows:

Bitmap Heap Scan on objects_hier=9A = (cost=3D2.10..102.75 rows=3D30 width=3D337) (actual time=3D0.028..0.028 rows=3D0 = loops=3D1)

=9A=9A Recheck Cond: (tg && = '{0}'::integer[])

=9A=9A ->=9A Bitmap Index Scan on = gistbla2=9A (cost=3D0.00..2.10 rows=3D !! 30 !! width=3D0) (actual = time=3D0.024..0.024 rows=3D0 loops=3D1)

=9A=9A=9A=9A=9A=9A=9A=9A Index Cond: (tg = && '{0}'::integer[])

See the number of estimated rows is 30 is all = cases.

But actually it varies from whole table (30000 = rows) to 0.

Looks like GIST indexes for intarray give no statistic at all.

It makes them much much less useless than they = could be.. Because planner can’t plan them well and makes horrible = mistakes.

For example, puts nested loops in order when = for each of 30k rows it makes an index scan within 5 rows =3D> that leads to = 30k nested scans, while it should for each of 5 rows perform single index scan = among those 30k.

Yes, I have all necessary indexes on = tables.

And yes, I run VACUUM FULL ANALYZE just before = the tests.

The lack of estimation is not documented = anywhere so I just hope this is a bug and can be fixed fast J

------=_NextPart_000_0017_01C5AC32.5E9A9B20--