Re: Problem (bug?) with like

El lunes 03 de diciembre, Tom Lane escribió:
> You sure you didn't paste in the same result for #2 and #3? They're
> the same plan with the same rows estimates --- but I'd expect the rows
> estimates, at least, to change given the more-selective LIKE pattern.

Ahem... You are right. It seems I have a problem with Vim and insertion of
command result :(

Here are the correct results:

-----------------------------------------------------------------------
1) # explain SELECT * from v_cliente where nombre like '%DA%';

Result:

NOTICE: QUERY PLAN:

Merge Join (cost=54763.50..62874.36 rows=413980 width=183)
-> Sort (cost=16238.44..16238.44 rows=54 width=131)
-> Seq Scan on cliente c (cost=0.00..16236.88 rows=54 width=131)
-> Sort (cost=38525.06..38525.06 rows=20097 width=74)
-> Subquery Scan d (cost=891.91..37088.66 rows=20097 width=74)
-> Hash Join (cost=891.91..37088.66 rows=20097 width=74)
-> Hash Join (cost=100.89..26377.49 rows=20097 width=58)
-> Merge Join (cost=78.96..17190.49 rows=20097 width=42)
-> Index Scan using dir_via_ndx on dirección d (cost=0.00..8951.65 rows=20097 width=26)
-> Sort (cost=78.96..78.96 rows=176 width=16)
-> Seq Scan on vía v (cost=0.00..72.40 rows=176 width=16)
-> Hash (cost=21.80..21.80 rows=52 width=16)
-> Seq Scan on provincia p (cost=0.00..21.80 rows=52 width=16)
-> Hash (cost=786.20..786.20 rows=1928 width=16)
-> Seq Scan on localidad l (cost=0.00..786.20 rows=1928 width=16)

EXPLAIN
---------------------------------------------------------------------------
2) explain SELECT * from v_cliente where nombre like '%DAVID%';

Result:

NOTICE: QUERY PLAN:

Nested Loop (cost=891.91..61414.58 rows=7638 width=183)
-> Seq Scan on cliente c (cost=0.00..16236.88 rows=1 width=131)
-> Subquery Scan d (cost=891.91..37088.66 rows=20097 width=74)
-> Hash Join (cost=891.91..37088.66 rows=20097 width=74)
-> Hash Join (cost=100.89..26377.49 rows=20097 width=58)
-> Merge Join (cost=78.96..17190.49 rows=20097 width=42)
-> Index Scan using dir_via_ndx on dirección d (cost=0.00..8951.65 rows=20097 width=26)
-> Sort (cost=78.96..78.96 rows=176 width=16)
-> Seq Scan on vía v (cost=0.00..72.40 rows=176 width=16)
-> Hash (cost=21.80..21.80 rows=52 width=16)
-> Seq Scan on provincia p (cost=0.00..21.80 rows=52 width=16)
-> Hash (cost=786.20..786.20 rows=1928 width=16)
-> Seq Scan on localidad l (cost=0.00..786.20 rows=1928 width=16)
EXPLAIN
----------------------------------------------------------------------------

> Also, how many rows are there really that match '%DA%' and '%DAVID%'?

1) 2672 rows -> 3.59 sec.
2) 257 rows -> 364.69 sec.

v_cliente and cliente have same number of rows: 38975

I hope this is enough. Greets.

David