Re: Problem (bug?) with like

El lunes 03 de diciembre, Tom Lane escribió:
> bombadil(at)wanadoo(dot)es writes:
> > select * from v_A where name like '%DAVID%'
> >
> > It freezes psql.
>
> I don't believe that it's really "frozen". Taking a long time, maybe.

Perhaps. But a veeeeeery long time, in any way ;)

I have been waiting more than 3 minutes and... ¡e voila!, here it is
:)

> > Why?
>
> You tell us. What's the EXPLAIN query plan for these three queries?

Ops. Sorry for laziness.

Here are my queries:

--------------------------------------------------------------------------
1) # explain SELECT * from cliente where nombre like '%DAVID%';

Result:

NOTICE: QUERY PLAN:

Seq Scan on cliente (cost=0.00..16139.44 rows=1 width=131)

--------------------------------------------------------------------------
2) # 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)

------------------------------------------------------------------------------
3) # explain SELECT * from v_cliente where nombre like '%DAVID%';

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)

--------------------------------------------------------------------------------

Greets.

David