From: Cédric Villemain <cedric.villemain@dalibo.com>
To: pgsql-performance@postgresql.org
Cc: Martin Kjeldsen <martin@martinkjeldsen.dk>
Subject: Re: Bad prepare performance
Date: Mon, 31 Mar 2008 13:59:11 +0200
Message-ID: <200803311359.17687.cedric.villemain@dalibo.com> (raw)
In-Reply-To: <20080331110013.GA6965@martinkjeldsen.dk>
References: <20080331110013.GA6965@martinkjeldsen.dk>
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.orgAttachments:
signature.asc (application/pgp-signature, 188B) download
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: cedric.villemain@dalibo.com, martin@martinkjeldsen.dk
Subject: Re: Bad prepare performance
In-Reply-To: <200803311359.17687.cedric.villemain@dalibo.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