From: | bombadil(at)wanadoo(dot)es |
---|---|
To: | Lista PostgreSql <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Problem (bug?) with like |
Date: | 2001-12-04 08:15:57 |
Message-ID: | 20011204081557.GA2832@fangorn.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | GB Clark II | 2001-12-04 08:53:21 | Re: Syslog |
Previous Message | ranjitha ullasa | 2001-12-04 06:09:51 | how to create a database |
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Page | 2001-12-04 08:31:48 | Re: FW: [CYGWIN] 7.2b3 postmaster doesn't start on Win98 |
Previous Message | Tom Lane | 2001-12-04 06:18:41 | Re: FW: [CYGWIN] 7.2b3 postmaster doesn't start on Win98 |