Why is the wrong index used? (with "gist" index) - Mailing list pgsql-general

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-