From: Albert Cervera Areny <albert@sedifa.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Posible planner improvement?
Date: Wed, 21 May 2008 13:11:24 +0200
Message-ID: <200805211311.25016.albert@sedifa.com> (raw)
In-Reply-To: <4833FDE5.8090603@archonet.com>
References: <200805211228.48248.albert@sedifa.com>
<4833FDE5.8090603@archonet.com>
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.
Attachments:queries.txt (text/plain, 2.4K) download
# 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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: albert@sedifa.com, dev@archonet.com
Subject: Re: Posible planner improvement?
In-Reply-To: <200805211311.25016.albert@sedifa.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox