Re: Posible planner improvement?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux