Hello
Just because I added a "LIMIT 1" to the following query, PostgreSQL decided to
use a different index which gives a far worse performance :-(
Probably it gets a bit confused because I use the custom index from the prefix
extension (https://github.com/dimitri/prefix/blob/master/README.txt).
If it's impossible for PostgreSQL to make a correct judgement here, can I force
it to use a certain index in cases where I know better?
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.253rows=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-
