From: | "Martin, Sylvain R(dot) (LNG)" <Sylvain(dot)Martin(at)lexis-nexis(dot)com> |
---|---|
To: | "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org> |
Subject: | bug in using index scans? |
Date: | 2000-07-05 14:56:21 |
Message-ID: | 7985A5B6EB66D311B6350008C791487A029BFB8A@lnxdayexch08.lexis-nexis.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Patrick Jacquot | 2000-07-05 15:00:51 | Re: GROUP by finish&&last day of month |
Previous Message | Thomas Lockhart | 2000-07-05 14:51:11 | Re: Aww, sorry (last day of month) |