Re: Problem (bug?) with like - Mailing list pgsql-general

El lunes 03 de diciembre, Tom Lane escribió:
> bombadil@wanadoo.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