like not using indexes in 7.3b5?
От | Lars |
---|---|
Тема | like not using indexes in 7.3b5? |
Дата | |
Msg-id | 3DCA2373.7070005@yahoo.com обсуждение исходный текст |
Ответы |
Re: like not using indexes in 7.3b5?
|
Список | pgsql-bugs |
I have a table with a bunch of varchar columns. In 7.2.3 Postgres would indexes on varchar column for certain like queries. This is a script from 7.2.3: ---------------------------- lars=> \d agency Table "agency" Column | Type | Modifiers ---------+-----------------------+----------- nr | integer | name1 | character varying(20) | name2 | character varying(20) | street | character varying(20) | city | character varying(10) | zip | character varying(10) | phone | character varying(15) | blocked | boolean | Indexes: agency_zip lars=> \d agency_zip Index "agency_zip" Column | Type --------+----------------------- zip | character varying(10) btree lars=> explain select * from agency where zip = 'abc'; NOTICE: QUERY PLAN: Index Scan using agency_zip on agency (cost=0.00..3.11 rows=8 width=90) EXPLAIN lars=> explain select * from agency where zip like 'abc'; NOTICE: QUERY PLAN: Index Scan using agency_zip on agency (cost=0.00..3.11 rows=8 width=90) EXPLAIN lars=> explain select * from agency where zip like 'abc%'; NOTICE: QUERY PLAN: Index Scan using agency_zip on agency (cost=0.00..3.01 rows=1 width=90) EXPLAIN The same table 7.3b5 results in this: ------------------------------------- lars=> explain select * from agency where zip = 'abc'; QUERY PLAN ------------------------------------------------------------------------------- Index Scan using agency_zip on agency (cost=0.00..393.64 rows=112 width=124) Index Cond: (zip = 'abc'::character varying) (2 rows) lars=> explain select * from agency where zip like 'abc'; QUERY PLAN ------------------------------------------------------------ Seq Scan on agency (cost=0.00..653.50 rows=112 width=124) Filter: (zip ~~ 'abc'::text) (2 rows) lars=> explain select * from agency where zip like 'abc%'; QUERY PLAN ------------------------------------------------------------ Seq Scan on agency (cost=0.00..653.50 rows=112 width=124) Filter: (zip ~~ 'abc%'::text) (2 rows) i.e. is correctly uses the index for the = operator but not for like (if used as prefix operator) In both cases the tables are "analyzed" and the like queries execute slower in 7.3b5. Is that expected behavior in 7.3? -- Lars
В списке pgsql-bugs по дате отправления: