3 ms·

All of the examples seem like relatively straight-forward transformations. I'm quite surprised that the PG query optimizer doesn't to those. How do other engin

All of the examples seem like relatively straight-forward transformations. I'm quite surprised that the PG query optimizer doesn't to those. How do other engines compare here?

gwd 7y ago
sqlite certainly seems to optimize it automaticaly; Note the 'IN operator' in the plan where none was in the query: sqlite> explain select * from words where wid=1 or wid=2; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 21 0 00 Start at 21 1 OpenRead 0 3 0 2 00 root=3 iDb=0; words 2 Integer 11 2 0 00 r[2]=11; return address 3 Once 0 11 0 00 4 OpenEphemeral 1 1 0 k(1,) 00 nColumn=1; RHS of IN operator 5 Integer 1 3 0 00 r[3]=1 6 MakeRecord 3 1 4 D 00 r[4]=mkrec(r[3]) 7 IdxInsert 1 4 3 1 00 key=r[4] 8 Integer 2 3 0 00 r[3]=2 9 MakeRecord 3 1 4 D 00 r[4]=mkrec(r[3]) 10 IdxInsert 1 4 3 1 00 key=r[4] 11 Return 2 0 0 00 12 Rewind 1 20 0 00 13 Column 1 0 1 00 r[1]= 14 IsNull 1 19 0 00 if r[1]==NULL goto 19 15 SeekRowid 0 19 1 00 intkey=r[1] 16 Rowid 0 5 0 00 r[5]=rowid 17 Column 0 1 6 00 r[6]=words.text 18 ResultRow 5 2 0 00 output=r[5..6] 19 Next 1 13 0 00 20 Halt 0 0 0 00 21 Transaction 0 0 97 0 01 usesStmtJournal=0 22 Goto 0 1 0 00 I've verified that "where wid in (1,2)" produces the same plan.

All of the examples seem like relatively straight-forward transformations. I'm quite surprised that the PG query optimizer doesn't to those. How do other engines compare here?

gwd 7y ago

sqlite certainly seems to optimize it automaticaly; Note the 'IN operator' in the plan where none was in the query: sqlite> explain select * from words where wid=1 or wid=2; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 21 0 00 Start at 21 1 OpenRead 0 3 0 2 00 root=3 iDb=0; words 2 Integer 11 2 0 00 r[2]=11; return address 3 Once 0 11 0 00 4 OpenEphemeral 1 1 0 k(1,) 00 nColumn=1; RHS of IN operator 5 Integer 1 3 0 00 r[3]=1 6 MakeRecord 3 1 4 D 00 r[4]=mkrec(r[3]) 7 IdxInsert 1 4 3 1 00 key=r[4] 8 Integer 2 3 0 00 r[3]=2 9 MakeRecord 3 1 4 D 00 r[4]=mkrec(r[3]) 10 IdxInsert 1 4 3 1 00 key=r[4] 11 Return 2 0 0 00 12 Rewind 1 20 0 00 13 Column 1 0 1 00 r[1]= 14 IsNull 1 19 0 00 if r[1]==NULL goto 19 15 SeekRowid 0 19 1 00 intkey=r[1] 16 Rowid 0 5 0 00 r[5]=rowid 17 Column 0 1 6 00 r[6]=words.text 18 ResultRow 5 2 0 00 output=r[5..6] 19 Next 1 13 0 00 20 Halt 0 0 0 00 21 Transaction 0 0 97 0 01 usesStmtJournal=0 22 Goto 0 1 0 00 I've verified that "where wid in (1,2)" produces the same plan.