Package: postgresql-9.1-prefix Version: 1.1.1-1 Severity: important Tags: upstream
Hello (As reported on http://comments.gmane.org/gmane.comp.db.postgresql.general/165885) Just because I added a "LIMIT 1" to the following query, PostgreSQL decided not to use the special GIST index but a different one or none at all which gives a far worse performance. I could only reproduce this bug with a big table of 20,000,000 rows. That though makes this bug so evil as it might happen suddenly in production when the number of rows reaches a certain threshold. According to Tom Lane it's probably due to the "dummy selectivity estimation". If I understood correctly then he is referring to the "contsel" function from prefix.sql which always returns 0.001 which seems quite low but still give 20,000 rows for such big tables. But this is just a guess. The index was created as follows, but the additional gist_prefix_range_ops parameter does not seem to have any effect: CREATE INDEX destinations_nr_gist_idx ON destinations USING gist (nr gist_prefix_range_ops); The table is 3-4GB big and contains some million rows: devel=# ANALYZE VERBOSE destinations; INFO: analyzing "public.destinations" INFO: "destinations": scanned 30000 of 196069 pages, containing 3205481 live rows and 441 dead rows; 30000 rows in sample, 20948720 estimated total rows devel=# explain analyze SELECT prefix FROM destinations WHERE nr @> '22116804109' ORDER BY length(nr) desc; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=63182.14..63234.51 rows=20949 width=22) (actual time=0.277..0.278 rows=2 loops=1) Sort Key: (length(nr)) Sort Method: quicksort Memory: 25kB -> Bitmap Heap Scan on destinations (cost=817.08..61678.57 rows=20949 width=22) (actual time=0.264..0.269 rows=2 loops=1) Recheck Cond: (nr @> '22116804109'::prefix_range) -> Bitmap Index Scan on destinations_nr_gist_idx (cost=0.00..811.84 rows=20949 width=0) (actual time=0.253..0.253 rows=2 loops=1) Index Cond: (nr @> '22116804109'::prefix_range) Total runtime: 0.315 ms ^^^^^^^^ GOOD! devel=# explain analyze SELECT prefix FROM destinations WHERE nr @> '22116804109' ORDER BY length(nr) desc LIMIT 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=0.00..3481.06 rows=1 width=22) (actual time=689.413..689.414 rows=1 loops=1) -> Index Scan Backward using destinations_nr_length_idx on destinations (cost=0.00..72924752.20 rows=20949 width=22) (actual time=689.410..689.410 rows=1 loops=1) Filter: (nr @> '22116804109'::prefix_range) Total runtime: 689.437 ms ^^^^^^^^^^ BAD! devel=# \d+ destinations; Table "public.destinations" Column | Type | Modifiers | Storage | Description -------------+-----------------------------+-----------------------------------------------------------+----------+---------------- id | integer | not null default nextval('destinations_id_seq'::regclass) | plain | nr | prefix_range | not null | plain | prefix | text | not null | extended | [...] Indexes: "destinations_pkey" PRIMARY KEY, btree (id) "destinations_nr_unique_idx" UNIQUE, btree (nr) "destinations_nr_gist_idx" gist (nr) "destinations_nr_length_idx" btree (length(nr)) "destinations_prefix_idx" btree (prefix) Has OIDs: no bye, -christian- -- System Information: Debian Release: wheezy/sid APT prefers testing APT policy: (500, 'testing') Architecture: i386 (i686) Kernel: Linux 3.2.0-3-686-pae (SMP w/2 CPU cores) Locale: LANG=de_DE.UTF-8, LC_CTYPE=de_DE.UTF-8 (charmap=UTF-8) Shell: /bin/sh linked to /bin/dash Versions of packages postgresql-9.1-prefix depends on: ii libc6 2.13-35 ii postgresql-9.1 9.1.5-2 postgresql-9.1-prefix recommends no packages. postgresql-9.1-prefix suggests no packages. -- no debconf information -- To UNSUBSCRIBE, email to [email protected] with a subject of "unsubscribe". Trouble? Contact [email protected]
