pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedintarray is broken ? (8.1b1)
2+ messages / 2 participants
[nested] [flat]
* intarray is broken ? (8.1b1)
@ 2005-08-28 20:41 Ilia Kantor <ilia@obnovlenie.ru>
2005-08-28 21:24 ` Re: intarray is broken ? (8.1b1) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Ilia Kantor @ 2005-08-28 20:41 UTC (permalink / raw)
To: pgsql-performance
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 :-)
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: intarray is broken ? (8.1b1)
2005-08-28 20:41 intarray is broken ? (8.1b1) Ilia Kantor <ilia@obnovlenie.ru>
@ 2005-08-28 21:24 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2005-08-28 21:24 UTC (permalink / raw)
To: Ilia Kantor <ilia@obnovlenie.ru>; +Cc: pgsql-performance
"Ilia Kantor" <ilia@obnovlenie.ru> writes:
> Looks like GIST indexes for intarray give no statistic at all.
Feel free to contribute some stats routines that aren't stubs ...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2005-08-28 21:24 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-28 20:41 intarray is broken ? (8.1b1) Ilia Kantor <ilia@obnovlenie.ru>
2005-08-28 21:24 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox