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.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- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
