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.
Right, attached an example of such a difference.
# 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)
