Re: Planner estimates cost of 'like' a lot lower than '='??

I just did a quick test before going to bed but 7.2devel seems
do to what we want:

testdb=> select count(*) from data;
count
--------
162135
(1 row)

testdb=> vacuum analyze data;
VACUUM
testdb=> explain select find0.key0, ret0.longValue as v0, ret1.longValue as v1 from (data find0 inner join data find1 using (key0)) left outer join data ret0 on (ret0.key0 = find0.key0 and ret0.key1 = 'uuuu' and ret0.key2 = 'number') left outer join data ret1 on (ret1.key0 = find0.key0 and ret1.key1 = 'test' and ret1.key2 = 'test') where find0.key1 = 'uuuu' and find0.key2 = 'llll' and find0.value like 't10k__' and find1.key1 = 'test' and find1.key2 = 'test' and find1.value like 'test_0';
NOTICE: QUERY PLAN:

Nested Loop (cost=0.00..24.05 rows=1 width=318)
-> Nested Loop (cost=0.00..18.02 rows=1 width=195)
-> Nested Loop (cost=0.00..11.98 rows=1 width=72)
-> Index Scan using datavalueindex on data find0 (cost=0.00..5.94 rows=1 width=36)
-> Index Scan using dataindex on data find1 (cost=0.00..6.03 rows=1 width=36)
-> Index Scan using dataindex on data ret0 (cost=0.00..6.02 rows=1 width=123)
-> Index Scan using dataindex on data ret1 (cost=0.00..6.02 rows=1 width=123)

EXPLAIN
testdb=> select now(); select find0.key0, ret0.longValue as v0, ret1.longValue as v1 from (data find0 inner join data find1 using (key0)) left outer join data ret0 on (ret0.key0 = find0.key0 and ret0.key1 = 'uuuu' and ret0.key2 = 'number') left outer join data ret1 on (ret1.key0 = find0.key0 and ret1.key1 = 'test' and ret1.key2 = 'test') where find0.key1 = 'uuuu' and find0.key2 = 'llll' and find0.value like 't10k__' and find1.key1 = 'test' and find1.key2 = 'test' and find1.value like 'test_0'; select now();
now
------------------------
2001-07-23 00:35:13+02
(1 row)

key0 | v0 | v1
----------------------------------+----+--------
8a7967698cae55e66e627969270c34d8 | 3 | test10
7e2d4eb1188d0e114bff6f0ccf658f59 | 3 | test20
f7c97d1ddafacc36faba09ef3be6ac9c | 3 | test30
e59c68a66f83b1fcdd8ec8e58a854fdb | 3 | test40
077cd901c5c9b88219e5c1d14acc7c41 | 3 | test50
36f6af71d8fa1331a3640675c1dd0cf7 | 3 | test60
bc0a3e2064508f70063516eb709c7654 | 3 | test70
34c376648ef62fce58e1d80f70f1327d | 3 | test80
127869c8452da6e1438795509380b946 | 3 | test90
(9 rows)

now
------------------------
2001-07-23 00:35:14+02
(1 row)

testdb=>
testdb=>
testdb=>
testdb=> select count(*) from data where value like 'test_0';
count
-------
9
(1 row)

testdb=> explain select count(*) from data where value like 'test_0';
NOTICE: QUERY PLAN:

Aggregate (cost=5581.45..5581.45 rows=1 width=0)
-> Seq Scan on data (cost=0.00..5580.69 rows=306 width=0)

EXPLAIN
testdb=> select count(*) from data where value >= 'test' and value < 'tesu';
count
-------
10000
(1 row)

testdb=> explain select count(*) from data where value >= 'test' and value < 'tesu';
NOTICE: QUERY PLAN:

Aggregate (cost=6007.24..6007.24 rows=1 width=0)
-> Seq Scan on data (cost=0.00..5986.02 rows=8487 width=0)

EXPLAIN
testdb=>

_
Mats Lofkvist
mal(at)algonet(dot)se