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

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