Обсуждение: Efficiency again...
Hi. I just noticed something interesting. I don't know if my idea is better or if it wasn't implemented because it violates some SQL rule... searchengine=> create table test ( test1 int4, test2 int4); CREATE searchengine=> create index test_itest1 on test (test1); CREATE <insert a pile of data so it looks like so> searchengine=> select * from test; test1|test2 -----+----- 1| 3 1| 5 1| 9 2| 1 2| 3 2| 6 2| 9 3| 9 4| 5 (9 rows) Now here is the plan I expect for a single test1 value searchengine=> explain select * from test where test1=1; Index Scan on test (cost=0.00 size=0 width=8) But look: searchengine=> explain select * from test where test1=1 or test1=2; Seq Scan on test (cost=0.00 size=0 width=8) ugh! Sequential. This may be OK for a small database, but in my application I have many rows: searchengine=> explain select * from word_detail where word_id=23423 or word_id=68548; Seq Scan on word_detail (cost=205938.73 size=510342 width=10) That costs a _LOT_. Wouldn't it be better to do n sequential scans where n is the number of or'd together values? Using IN doesn't help out either... searchengine=> explain select * from test where test1 IN (5,9); Seq Scan on test (cost=0.00 size=0 width=8) Sometimes I wish I had the power to tell the DBMS how I wanted a query done... -Mike
> Now here is the plan I expect for a single test1 value > searchengine=> explain select * from test where test1=1; > Index Scan on test (cost=0.00 size=0 width=8) > > But look: > searchengine=> explain select * from test where test1=1 or test1=2; > Seq Scan on test (cost=0.00 size=0 width=8) > > ugh! Sequential. This may be OK for a small database, but in my > application I have many rows: > searchengine=> explain select * from word_detail where word_id=23423 or > word_id=68548; > > Seq Scan on word_detail (cost=205938.73 size=510342 width=10) > > That costs a _LOT_. > > Wouldn't it be better to do n sequential scans where n is the number of > or'd together values? Using IN doesn't help out either... > > searchengine=> explain select * from test where test1 IN (5,9); > Seq Scan on test (cost=0.00 size=0 width=8) > > Sometimes I wish I had the power to tell the DBMS how I wanted a query > done... Yep, it is on our TODO list, and we have someone trying some fix for 6.4. It has to do the conjunctive normal form(cnf). -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)