From: Albert Cervera Areny <albert@sedifa.com>
Organization: Sedifa, S.L.
To: Mark Mielke <mark@mark.mielke.cc>
Subject: Re: Posible planner improvement?
Date: Wed, 21 May 2008 13:30:16 +0200
User-Agent: KMail/1.9.9
Cc: Richard Huxton <dev@archonet.com>,
 pgsql-performance@postgresql.org
References: <200805211228.48248.albert@sedifa.com>
	<200805211311.25016.albert@sedifa.com>
	<48340687.9010501@mark.mielke.cc>
In-Reply-To: <48340687.9010501@mark.mielke.cc>
MIME-Version: 1.0
Content-Type: Multipart/Mixed;
  boundary="Boundary-00=_IfANIU2WkZzBM0E"
Message-Id: <200805211330.16604.albert@sedifa.com>

--Boundary-00=_IfANIU2WkZzBM0E
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

A Dimecres 21 Maig 2008, Mark Mielke va escriure:
> A Dimecres 21 Maig 2008, Richard Huxton va escriure:
> >> Albert Cervera Areny wrote:
> >>> I've got a query similar to this:
> >>>
> >>> select * from t1, t2 where t1.id > 158507 and t1.id = t2.id;
> >>>
> >>> That took > 84 minutes (the query was a bit longer but this is the part
> >>> that made the difference) after a little change the query took ~1
> >>> second:
> >>>
> >>> select * from t1, t2 where t1.id > 158507 and t2.id > 158507 and t1.id
> >>> = t2.id;
> >>
> >> Try posting EXPLAIN ANALYSE SELECT ... for both of those queries and
> >> we'll see why it's better at the second one.
>
> Even if the estimates were off (they look a bit off for the first
> table), the above two queries are logically identical, and I would
> expect the planner to make the same decision for both.
>
> I am curious - what is the result of:
>
>     select * from t1, t2 where t2.id > 158507 and t1.id = t2.id;
>
> Is it the same speed as the first or second, or is a third speed entirely?

Attached the same file with the third result at the end. The result is worst 
than the other two cases. Note that I've analyzed both tables but results are 
the same. One order of magnitude between the two first queries.

>
> If t1.id = t2.id, I would expect the planner to substitute them freely
> in terms of identities?
>
> Cheers,
> mark


--Boundary-00=_IfANIU2WkZzBM0E
Content-Type: text/plain;
  charset="iso-8859-1";
  name="queries.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename="queries.txt"

# explain analyze select * from datos_venta dv, key_conta kc where dv.codigo > 158507 and kc.codigo> 158507 and dv.codigo=kc.codigo;

                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=7527.14..18203.25 rows=51415 width=253) (actual time=244.107..326.375 rows=11650 loops=1)
   Hash Cond: (dv.codigo = kc.codigo)
   ->  Bitmap Heap Scan on datos_venta dv  (cost=2852.89..9929.95 rows=154245 width=117) (actual time=4.801..25.601 rows=11650 loops=1)
         Recheck Cond: (codigo > 158507)
         ->  Bitmap Index Scan on datos_venta_idx  (cost=0.00..2852.89 rows=154245 width=0) (actual time=4.694..4.694 rows=11650 loops=1)
               Index Cond: (codigo > 158507)
   ->  Hash  (cost=4539.78..4539.78 rows=53788 width=136) (actual time=238.852..238.852 rows=2985 loops=1)
         ->  Bitmap Heap Scan on key_conta kc  (cost=999.43..4539.78 rows=53788 width=136) (actual time=102.208..174.339 rows=2985 loops=1)
               Recheck Cond: (codigo > 158507)
               ->  Bitmap Index Scan on key_conta_pkey  (cost=0.00..999.43 rows=53788 width=0) (actual time=102.137..102.137 rows=2985 loops=1)
                     Index Cond: (codigo > 158507)
 Total runtime: 347.949 ms
(12 rows)


# explain analyze select * from datos_venta dv, key_conta kc where dv.codigo > 158507 and dv.codigo=kc.codigo;
                                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=7737.95..21585.51 rows=154245 width=253) (actual time=2227.439..2991.082 rows=11650 loops=1)
   Hash Cond: (dv.codigo = kc.codigo)
   ->  Bitmap Heap Scan on datos_venta dv  (cost=2852.89..9929.95 rows=154245 width=117) (actual time=22.179..149.532 rows=11650 loops=1)
         Recheck Cond: (codigo > 158507)
         ->  Bitmap Index Scan on datos_venta_idx  (cost=0.00..2852.89 rows=154245 width=0) (actual time=22.086..22.086 rows=11650 loops=1
               Index Cond: (codigo > 158507)
   ->  Hash  (cost=4481.65..4481.65 rows=161365 width=136) (actual time=2205.184..2205.184 rows=161365 loops=1)
         ->  Seq Scan on key_conta kc  (cost=0.00..4481.65 rows=161365 width=136) (actual time=25.736..1351.018 rows=161365 loops=1)
 Total runtime: 3307.621 ms
(9 rows)


# explain analyze select * from datos_venta dv, key_conta kc where kc.codigo > 158507 and dv.codigo=kc.codigo;
                                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=146.18..14631.18 rows=8130 width=168) (actual time=1419.986..1555.975 rows=11650 loops=1)
   Hash Cond: (dv.codigo = kc.codigo)
   ->  Seq Scan on datos_venta dv  (cost=0.00..9776.35 rows=462735 width=59) (actual time=0.044..934.853 rows=462735 loops=1)
   ->  Hash  (cost=139.09..139.09 rows=2835 width=109) (actual time=14.487..14.487 rows=2985 loops=1)
         ->  Index Scan using key_conta_pkey on key_conta kc  (cost=0.00..139.09 rows=2835 width=109) (actual time=0.100..7.847 rows=2985
               Index Cond: (codigo > 158507)
 Total runtime: 1565.255 ms
(7 rows)


--Boundary-00=_IfANIU2WkZzBM0E--