From: =?iso-8859-1?q?C=E9dric_Villemain?= <cedric.villemain@dalibo.com>
Organization: dalibo.com
To: pgsql-performance@postgresql.org
Subject: Re: Bad prepare performance
Date: Mon, 31 Mar 2008 13:59:11 +0200
User-Agent: KMail/1.9.9
Cc: Martin Kjeldsen <martin@martinkjeldsen.dk>
References: <20080331110013.GA6965@martinkjeldsen.dk>
In-Reply-To: <20080331110013.GA6965@martinkjeldsen.dk>
MIME-Version: 1.0
Content-Type: multipart/signed; boundary="nextPart1721005.1ARIdoQ94k";
	protocol="application/pgp-signature"; micalg=pgp-sha1
Content-Transfer-Encoding: 7bit
Message-Id: <200803311359.17687.cedric.villemain@dalibo.com>

--nextPart1721005.1ARIdoQ94k
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Le Monday 31 March 2008, Martin Kjeldsen a =E9crit=A0:
> 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 automatical=
ly
> 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.=20

Did you saw this option :

  $sth =3D $dbh->prepare("SELECT id FROM mytable WHERE val =3D ?",
                       { pg_server_prepare =3D> 0 });

Then, *this* query will not be prepared by the server.

> Is there any planner hints I can use or anything happened or=20
> 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);
>                                                                          =
 =20
>           QUERY PLAN
> -------------------------------------------------------------------------=
=2D-
>--------------------------------------------------------------------------=
=2D-
>------------------------------- Limit  (cost=3D1143870.95..1143878.45
> rows=3D3000 width=3D267) (actual time=3D83033.101..83033.111 rows=3D4 loo=
ps=3D1) ->=20
> Sort  (cost=3D1143870.95..1148074.36 rows=3D1681367 width=3D267) (actual
> time=3D83033.099..83033.103 rows=3D4 loops=3D1) Sort Key: rt_trap.created
>          ->  Merge Left Join  (cost=3D0.00..829618.73 rows=3D1681367 widt=
h=3D267)
> (actual time=3D83032.946..83033.051 rows=3D4 loops=3D1) Merge Cond: (rt_t=
rap.guid
> =3D tp.trap_guid)
>                ->  Index Scan using idx_rt_trap_guid on rt_trap=20
> (cost=3D0.00..81738.88 rows=3D1681367 width=3D192) (actual time=3D0.012..=
0.020
> rows=3D4 loops=3D1) Index Cond: (guid > $1)
>                      Filter: (deleted IS NULL)
>                ->  Index Scan using idx_rt_trap_param_trap_guid on
> rt_trap_param tp  (cost=3D0.00..706147.04 rows=3D4992440 width=3D79) (act=
ual
> time=3D6.523..78594.750 rows=3D5044927 loops=3D1) Filter: (param_oid =3D
> 'snmpTrapOID.0'::text)
>  Total runtime: 83033.411 ms
> (11 rows)
>
> dmon2=3D# EXPLAIN ANALYZE SELECT * FROM v_rt_trap_detailed WHERE guid >
> 116505531 ORDER BY created LIMIT 3000; QUERY PLAN
> -------------------------------------------------------------------------=
=2D-
>--------------------------------------------------------------------------=
=2D-
>------------- Limit  (cost=3D9866.45..9867.71 rows=3D504 width=3D267) (act=
ual
> time=3D0.590..0.616 rows=3D12 loops=3D1) ->  Sort  (cost=3D9866.45..9867.=
71
> rows=3D504 width=3D267) (actual time=3D0.587..0.596 rows=3D12 loops=3D1) =
Sort Key:
> rt_trap.created
>          ->  Nested Loop Left Join  (cost=3D0.00..9843.83 rows=3D504 widt=
h=3D267)
> (actual time=3D0.157..0.531 rows=3D12 loops=3D1) ->  Index Scan using
> idx_rt_trap_guid on rt_trap  (cost=3D0.00..26.78 rows=3D504 width=3D192) =
(actual
> time=3D0.022..0.034 rows=3D12 loops=3D1) Index Cond: (guid > 116505531)
>                      Filter: (deleted IS NULL)
>                ->  Index Scan using idx_rt_trap_param_trap_guid on
> rt_trap_param tp  (cost=3D0.00..18.36 rows=3D89 width=3D79) (actual
> time=3D0.006..0.009 rows=3D1 loops=3D12) Index Cond: (rt_trap.guid =3D
> tp.trap_guid)
>                      Filter: (param_oid =3D 'snmpTrapOID.0'::text)
>  Total runtime: 0.733 ms
> (11 rows)


=2D-=20
C=E9dric Villemain
Administrateur de Base de Donn=E9es
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org

--nextPart1721005.1ARIdoQ94k
Content-Type: application/pgp-signature; name=signature.asc 
Content-Description: This is a digitally signed message part.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQBH8NIVo/dppWjpEvwRAu1pAKDIpJFjG2ZDvm5pRZTnTBl4nwl4QwCgjs5X
JCYWDFsQu3GH9Njwsrp3chs=
=hHdY
-----END PGP SIGNATURE-----

--nextPart1721005.1ARIdoQ94k--