Re: Planner estimates cost of 'like' a lot lower than '='?? - Mailing list pgsql-general

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
outerjoin data ret1 on (ret1.key0 = find0.key0 and ret1.key1 = 'test' and ret1.key2 = 'test') where find0.key1 = 'uuuu'
andfind0.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
find1using (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@algonet.se