Thread: Bad prepare performance
Hello,
I'm sorry if this has been discussed before, I couldn't find a discussion about
this problem.
I've done the same query on a 8.2.5 database. The first one is prepared first
and the other is executed directly.
I understand why the there is such a great difference between the two ways of
executing the query (postgres has no way of knowing that $1 will be quite big
and that the result is not too big).
I could just avoid using prepare statements, but this is done automatically with
Perl's DBD::Pg. I know how to avoid using prepare statements (avoid having
placeholders in the statement), but that is not the prettiest of work arounds.
Is there any planner hints I can use or anything happened or happening with 8.3
or later that I can use?
Thank you in advance and the following is the EXPLAIN ANALYZE for the queries.
Best regards
Martin Kjeldsen
-----
PREPARE test_x (INT) AS SELECT * FROM v_rt_trap_detailed WHERE guid > $1 ORDER BY created LIMIT 3000;
EXPLAIN ANALYZE EXECUTE test_x (116505531);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1143870.95..1143878.45 rows=3000 width=267) (actual time=83033.101..83033.111 rows=4 loops=1)
-> Sort (cost=1143870.95..1148074.36 rows=1681367 width=267) (actual time=83033.099..83033.103 rows=4 loops=1)
Sort Key: rt_trap.created
-> Merge Left Join (cost=0.00..829618.73 rows=1681367 width=267) (actual time=83032.946..83033.051 rows=4
loops=1)
Merge Cond: (rt_trap.guid = tp.trap_guid)
-> Index Scan using idx_rt_trap_guid on rt_trap (cost=0.00..81738.88 rows=1681367 width=192) (actual
time=0.012..0.020rows=4 loops=1)
Index Cond: (guid > $1)
Filter: (deleted IS NULL)
-> Index Scan using idx_rt_trap_param_trap_guid on rt_trap_param tp (cost=0.00..706147.04 rows=4992440
width=79)(actual time=6.523..78594.750 rows=5044927 loops=1)
Filter: (param_oid = 'snmpTrapOID.0'::text)
Total runtime: 83033.411 ms
(11 rows)
dmon2=# EXPLAIN ANALYZE SELECT * FROM v_rt_trap_detailed WHERE guid > 116505531 ORDER BY created LIMIT 3000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=9866.45..9867.71 rows=504 width=267) (actual time=0.590..0.616 rows=12 loops=1)
-> Sort (cost=9866.45..9867.71 rows=504 width=267) (actual time=0.587..0.596 rows=12 loops=1)
Sort Key: rt_trap.created
-> Nested Loop Left Join (cost=0.00..9843.83 rows=504 width=267) (actual time=0.157..0.531 rows=12 loops=1)
-> Index Scan using idx_rt_trap_guid on rt_trap (cost=0.00..26.78 rows=504 width=192) (actual
time=0.022..0.034rows=12 loops=1)
Index Cond: (guid > 116505531)
Filter: (deleted IS NULL)
-> Index Scan using idx_rt_trap_param_trap_guid on rt_trap_param tp (cost=0.00..18.36 rows=89
width=79)(actual time=0.006..0.009 rows=1 loops=12)
Index Cond: (rt_trap.guid = tp.trap_guid)
Filter: (param_oid = 'snmpTrapOID.0'::text)
Total runtime: 0.733 ms
(11 rows)
Le Monday 31 March 2008, Martin Kjeldsen a écrit :
> Hello,
>
> I'm sorry if this has been discussed before, I couldn't find a discussion
> about this problem.
>
> I've done the same query on a 8.2.5 database. The first one is prepared
> first and the other is executed directly.
>
> I understand why the there is such a great difference between the two ways
> of executing the query (postgres has no way of knowing that $1 will be
> quite big and that the result is not too big).
>
> I could just avoid using prepare statements, but this is done automatically
> with Perl's DBD::Pg. I know how to avoid using prepare statements (avoid
> having placeholders in the statement), but that is not the prettiest of
> work arounds.
Did you saw this option :
$sth = $dbh->prepare("SELECT id FROM mytable WHERE val = ?",
{ pg_server_prepare => 0 });
Then, *this* query will not be prepared by the server.
> Is there any planner hints I can use or anything happened or
> happening with 8.3 or later that I can use?
>
> Thank you in advance and the following is the EXPLAIN ANALYZE for the
> queries.
>
> Best regards
>
>
> Martin Kjeldsen
>
> -----
>
> PREPARE test_x (INT) AS SELECT * FROM v_rt_trap_detailed WHERE guid > $1
> ORDER BY created LIMIT 3000;
>
> EXPLAIN ANALYZE EXECUTE test_x (116505531);
>
> QUERY PLAN
> ---------------------------------------------------------------------------
>----------------------------------------------------------------------------
>------------------------------- Limit (cost=1143870.95..1143878.45
> rows=3000 width=267) (actual time=83033.101..83033.111 rows=4 loops=1) ->
> Sort (cost=1143870.95..1148074.36 rows=1681367 width=267) (actual
> time=83033.099..83033.103 rows=4 loops=1) Sort Key: rt_trap.created
> -> Merge Left Join (cost=0.00..829618.73 rows=1681367 width=267)
> (actual time=83032.946..83033.051 rows=4 loops=1) Merge Cond: (rt_trap.guid
> = tp.trap_guid)
> -> Index Scan using idx_rt_trap_guid on rt_trap
> (cost=0.00..81738.88 rows=1681367 width=192) (actual time=0.012..0.020
> rows=4 loops=1) Index Cond: (guid > $1)
> Filter: (deleted IS NULL)
> -> Index Scan using idx_rt_trap_param_trap_guid on
> rt_trap_param tp (cost=0.00..706147.04 rows=4992440 width=79) (actual
> time=6.523..78594.750 rows=5044927 loops=1) Filter: (param_oid =
> 'snmpTrapOID.0'::text)
> Total runtime: 83033.411 ms
> (11 rows)
>
> dmon2=# EXPLAIN ANALYZE SELECT * FROM v_rt_trap_detailed WHERE guid >
> 116505531 ORDER BY created LIMIT 3000; QUERY PLAN
> ---------------------------------------------------------------------------
>----------------------------------------------------------------------------
>------------- Limit (cost=9866.45..9867.71 rows=504 width=267) (actual
> time=0.590..0.616 rows=12 loops=1) -> Sort (cost=9866.45..9867.71
> rows=504 width=267) (actual time=0.587..0.596 rows=12 loops=1) Sort Key:
> rt_trap.created
> -> Nested Loop Left Join (cost=0.00..9843.83 rows=504 width=267)
> (actual time=0.157..0.531 rows=12 loops=1) -> Index Scan using
> idx_rt_trap_guid on rt_trap (cost=0.00..26.78 rows=504 width=192) (actual
> time=0.022..0.034 rows=12 loops=1) Index Cond: (guid > 116505531)
> Filter: (deleted IS NULL)
> -> Index Scan using idx_rt_trap_param_trap_guid on
> rt_trap_param tp (cost=0.00..18.36 rows=89 width=79) (actual
> time=0.006..0.009 rows=1 loops=12) Index Cond: (rt_trap.guid =
> tp.trap_guid)
> Filter: (param_oid = 'snmpTrapOID.0'::text)
> Total runtime: 0.733 ms
> (11 rows)
--
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org
Attachment
Hi Martin, please CC the mailing-list,
then others can repply ;)
Cédric Villemain (13:59 2008-03-31):
> Le Monday 31 March 2008, Martin Kjeldsen a écrit :
> > I've done the same query on a 8.2.5 database. The first one is prepared
> > first and the other is executed directly.
> >
> > I understand why the there is such a great difference between the two ways
> > of executing the query (postgres has no way of knowing that $1 will be
> > quite big and that the result is not too big).
> >
> > I could just avoid using prepare statements, but this is done
automatically
> > with Perl's DBD::Pg. I know how to avoid using prepare statements (avoid
> > having placeholders in the statement), but that is not the prettiest of
> > work arounds.
>
> Did you saw this option :
>
> $sth = $dbh->prepare("SELECT id FROM mytable WHERE val = ?",
> { pg_server_prepare => 0 });
>
> Then, *this* query will not be prepared by the server.
This works very well. Thanks!
Still I regard this as a work around and the optimal solution would be to
allow the prepare statement to be prepared with an max(guid) is close to $1
hint or something like that.
I heard something about delayed prepare, where the prepared statements is
prepared on first use, this would solve my problem. Is this something being
work on right now?
Best regards
Martin Kjeldsen