Query appears not to recognise index (enable_seqscan=off)
От | Neil Saunders |
---|---|
Тема | Query appears not to recognise index (enable_seqscan=off) |
Дата | |
Msg-id | ddcd549e0904280931g70e61b82pee7abe4eefe41cd8@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Query appears not to recognise index (enable_seqscan=off)
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-novice |
Hi All, I'm attempting to implement an auto complete text field on a site I run. The plan is to return a maximum of 5 rows where the name of the location *begins* with the contexts of the text field (case insensitive). The location table contains data for 2182293 locations. I'm matching based on the "name" column. Before we dive in, this is Postgres 8.2.4. Table description: db=> \d gis_regions Table "public.gis_regions" Column | Type | Modifiers -----------------+------------------------+---------------------------------------------------------- id | integer | not null default nextval('gis_regions_id_seq'::regclass) name | character varying(200) | not null realname | character varying(200) | parent_id | integer | not null lft | integer | rgt | integer | town_id | integer | fipscode | character(2) | anomolytown | boolean | property_count | integer | default 0 link_count | integer | default 0 hierachy_string | text | hash | character varying(32) | regen_map | boolean | the_geom | geometry | Indexes: "idx_gisregions_id" PRIMARY KEY, btree (id) "idx_gis_regions_hash" UNIQUE, btree (hash) "gis_regions_idx_lftrgt" btree (lft, rgt) "gis_regions_idx_linkcount" btree (link_count) "gis_regions_idx_parentid" btree (parent_id, property_count) "gis_regions_idx_rgtlft" btree (rgt, lft) "gis_regions_idx_townid" btree (town_id) "idx_dmetaphone_name" btree (dmetaphone(name::text)) "idx_gis_regions" gist (the_geom) "idx_name" btree (lower(name::text)) Check constraints: "enforce_dims_the_geom" CHECK (ndims(the_geom) = 2) "enforce_srid_the_geom" CHECK (srid(the_geom) = -1) I added the "idx_name" index with the aim of optimizing the following query: SELECT name from gis_regions where lower(name) LIKE 'teign%' LIMIT 5; name --------------- Teign Village Teigngrace Teigny Teignmouth (4 rows) Time: 45318.544 ms 45 Seconds is much longer than I'd anticipated; no problem - to the explain plan: db=> EXPLAIN SELECT name from gis_regions where lower(name) LIKE 'teign%' LIMIT 5; QUERY PLAN ---------------------------------------------------------------------- Limit (cost=0.00..87606.40 rows=1 width=13) -> Seq Scan on gis_regions (cost=0.00..87606.40 rows=1 width=13) Filter: (lower((name)::text) ~~ 'teign%'::text) (3 rows) So we're seq scanning. I unsuccessfully attempted to tweak the optimiser costs to see if I could get it to use the index before setting enable_seq_scan = false and re-running the query: db=> EXPLAIN SELECT name from gis_regions where lower(name) LIKE 'teign%' LIMIT 5; QUERY PLAN ---------------------------------------------------------------------------------- Limit (cost=100000000.00..100087606.39 rows=1 width=13) -> Seq Scan on gis_regions (cost=100000000.00..100087606.39 rows=1 width=13) Filter: (lower((name)::text) ~~ 'teign%'::text) (3 rows) And we're still running the sequential scan - My questions is why? Both of the examples above were run with all the default optimiser costs. The only thing that I can conclude is that it's something to do with the datatype of "name" (VARCHAR(200)) but haven't been able to find an explanation with the usual googling. As an aside I'd like to upgrade to 8.3 to take advantage of the new index ordering, but in the mean time I'd settle for making use of an index :) Any assistance gratefully received. Kind Regards, Neil.
В списке pgsql-novice по дате отправления:
Предыдущее
От: "Preetam Palwe"Дата:
Сообщение: Re: Re: Copying data from one table of one database to other table f other database
Следующее
От: Tom LaneДата:
Сообщение: Re: Query appears not to recognise index (enable_seqscan=off)