Your message dated Wed, 23 Jan 2019 09:51:32 +0100
with message-id <[email protected]>
and subject line Re: Bug#690208: postgresql-9.1-prefix: Sometimes GIST index is
not used due to wrong row estimation.
has caused the Debian Bug report #690208,
regarding postgresql-9.1-prefix: Sometimes GIST index is not used due to wrong
row estimation.
to be marked as done.
This means that you claim that the problem has been dealt with.
If this is not the case it is now your responsibility to reopen the
Bug report if necessary, and/or fix the problem forthwith.
(NB: If you are a system administrator and have no idea what this
message is talking about, this may indicate a serious mail system
misconfiguration somewhere. Please contact [email protected]
immediately.)
--
690208: https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=690208
Debian Bug Tracking System
Contact [email protected] with problems
--- Begin Message ---
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
--- End Message ---
--- Begin Message ---
Re: Christian Hammers 2012-10-11
<[email protected]>
> 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))
Hi,
I think there's a very easy fix here: drop the length(nr) index. As
you will probably never look up prefixes by length, the only purpose
of this index is to enable sorting by length. But as the number of
results from a prefix lookup should be very small, sorting the result
is easy without the index. In practise, you'll never want the index to
be used like that.
If my analysis is wrong, we should move this to
https://github.com/dimitri/prefix/issues.
Christoph
--- End Message ---
_______________________________________________
pkg-postgresql-public mailing list
[email protected]
https://alioth-lists.debian.net/cgi-bin/mailman/listinfo/pkg-postgresql-public
