RE: bug in using index scans? More Investigating
От | Martin, Sylvain R. (LNG) |
---|---|
Тема | RE: bug in using index scans? More Investigating |
Дата | |
Msg-id | 7985A5B6EB66D311B6350008C791487A029BFB8B@lnxdayexch08.lexis-nexis.com обсуждение исходный текст |
Список | pgsql-sql |
just for the heck of it I decided to run the following in case it helped rvbs=# explain select count(*) from PI_Keywords where keyword like 'peripherals & access%'; NOTICE: QUERY PLAN: Aggregate (cost=35.98..35.98 rows=1 width=4) -> Index Scan using pi_keywords_idx on pi_keywords (cost=0.00..4.40 rows=12632 width=4) EXPLAIN rvbs=# explain select count(*) from PI_Keywords where keyword like 'peripherals & access'; NOTICE: QUERY PLAN: Aggregate (cost=6599.33..6599.33 rows=1 width=4) -> Seq Scan on pi_keywords (cost=0.00..6596.18 rows=1263 width=4) EXPLAIN Apparently adding a % at the end made it use the index scan. -----Original Message----- From: Martin, Sylvain R. (LNG) [mailto:Sylvain.Martin@lexis-nexis.com] Sent: Wednesday, July 05, 2000 10:56 AM To: 'pgsql-sql@postgresql.org' Subject: [SQL] bug in using index scans? I've notice on certain queries, I was waiting a long time for a return so I set out to troubleshoot something here's what I ran into... When I do a explain on a select looking for 'peripherals & access' it uses sequential scan but any other keyword uses index scan. I've ran the vacuum analyze on PI_Keywords but comes up with the same results. Can anyone offer some insight or confirm this as a bug? rvbs=# \d PI_Keywords Table "pi_keywords"Attribute | Type | Modifier -----------+----------+----------keyword | char(50) | productid | integer | Index: pi_keywords_idx rvbs=# select distinct (keyword) from PI_Keywords where keyword like '%&%' limit 10; keyword ----------------------------------------------------adult training & ed atlas & mapping books & manuals chips & modules education & training peripherals & access (6 rows) rvbs=# explain select * from PI_Keywords where keyword like 'peripherals & access'; NOTICE: QUERY PLAN: Seq Scan on pi_keywords (cost=0.00..6596.18 rows=1263 width=16) EXPLAIN rvbs=# explain select * from PI_Keywords where keyword like 'chips & modules'; NOTICE: QUERY PLAN: Index Scan using pi_keywords_idx on pi_keywords (cost=0.00..3652.83 rows=1263 width=16) EXPLAIN rvbs=# explain select * from PI_Keywords where keyword like 'education & training'; NOTICE: QUERY PLAN: Index Scan using pi_keywords_idx on pi_keywords (cost=0.00..3652.83 rows=1263 width=16) EXPLAIN rvbs=# explain select * from PI_Keywords where keyword like 'adult training & ed'; NOTICE: QUERY PLAN: Index Scan using pi_keywords_idx on pi_keywords (cost=0.00..3652.83 rows=1263 width=16) EXPLAIN rvbs=# explain select * from PI_Keywords where keyword like 'peripherals &%'; NOTICE: QUERY PLAN: Index Scan using pi_keywords_idx on pi_keywords (cost=0.00..4.40 rows=12632 width=16) EXPLAIN rvbs=# select distinct(keyword) from PI_Keywords where keyword like 'peripherals &%'; keyword ----------------------------------------------------peripherals & access (1 row) Sylvain Martin USA-Response Team (937) 865-6800 x4432 Pager: (937) 636-1171
В списке pgsql-sql по дате отправления: