Hello,
We have a strange issue related to a prepared statement. We have two equals queries where the sole difference is in the limit. - The first is hard coded with limit 500. - The second is prepared with limit $1 ($1 is bound to 500). PostgreSQL give us two different plans with a huge execution time for the prepared query: ----------------------------------------------------------------------------------------------------------------------- 2- Static Query ----------------------------------------------------------------------------------------------------------------------- explain analyze select * from dm2_lignecommandevente lignecomma0_ inner join dm2_lignedocumentcommercialvente lignecomma0_1_ on lignecomma0_.id=lignecomma0_1_.id inner join dm1_lignedocumentcommercial lignecomma0_2_ on lignecomma0_.id=lignecomma0_2_.id where (lignecomma0_.id not like 'DefaultRecord_%') and (lignecomma0_2_.dateFinValidite is null) order by coalescedate(lignecomma0_2_.dateCreationSysteme) desc limit 500 ------------------- Static query plan ------------------- Limit (cost=0.00..12165.11 rows=500 width=909) (actual time=73.477..90.256 rows=500 loops=1) -> Nested Loop (cost=0.00..11241165.90 rows=462025 width=909) (actual time=73.475..90.164 rows=500 loops=1) -> Nested Loop (cost=0.00..9086881.29 rows=462025 width=852) (actual time=4.105..11.749 rows=500 loops=1) -> Index Scan Backward using x_dm1_lignedocumentcommercial_14 on dm1_lignedocumentcommercial lignecomma0_2_ (cost=0.00..2744783.31 rows=1652194 width=541) (actual time=0.017..1.374 rows=1944 loops=1) Filter: (datefinvalidite IS NULL) -> Index Scan using dm2_lignecommandevente_pkey on dm2_lignecommandevente lignecomma0_ (cost=0.00..3.83 rows=1 width=311) (actual time=0.004..0.004 rows=0 loops=1944) Index Cond: ((lignecomma0_.id)::text = (lignecomma0_2_.id)::text) Filter: ((lignecomma0_.id)::text !~~ 'DefaultRecord_%'::text) -> Index Scan using dm2_lignedocumentcommercialvente_pkey on dm2_lignedocumentcommercialvente lignecomma0_1_ (cost=0.00..4.40 rows=1 width=57) (actual time=0.005..0.005 rows=1 loops=500) Index Cond: ((lignecomma0_1_.id)::text = (lignecomma0_.id)::text) Total runtime: 90.572 ms ----------------------------------------------------------------------------------------------------------------------- 2- Prepared Query ------------------------------------------------------------ ----------------------------------------------------------- PREPARE query(int) AS select * from dm2_lignecommandevente lignecomma0_ inner join dm2_lignedocumentcommercialvente lignecomma0_1_ on lignecomma0_.id=lignecomma0_1_.id inner join dm1_lignedocumentcommercial lignecomma0_2_ on lignecomma0_.id=lignecomma0_2_.id where (lignecomma0_.id not like 'DefaultRecord_%') and (lignecomma0_2_.dateFinValidite is null) order by coalescedate(lignecomma0_2_.dateCreationSysteme) desc limit $1; explain analyze execute query(500); ------------------- Prepared query plan ------------------- Limit (cost=879927.25..880042.76 rows=46202 width=909) (actual time=69609.593..69609.642 rows=500 loops=1) -> Sort (cost=879927.25..881082.32 rows=462025 width=909) (actual time=69609.588..69609.610 rows=500 loops=1) Sort Key: (coalescedate(lignecomma0_2_.datecreationsysteme)) Sort Method: top-N heapsort Memory: 498kB -> Hash Join (cost=164702.90..651691.22 rows=462025 width=909) (actual time=7786.467..68148.530 rows=470294 loops=1) Hash Cond: ((lignecomma0_2_.id)::text = (lignecomma0_.id)::text) -> Seq Scan on dm1_lignedocumentcommercial lignecomma0_2_ (cost=0.00..102742.36 rows=1652194 width=541) (actual time=0.009..50840.692 rows=1650554 loops=1) Filter: (datefinvalidite IS NULL) -> Hash (cost=136181.67..136181.67 rows=472579 width=368) (actual time=7681.787..7681.787 rows=472625 loops=1) -> Hash Join (cost=40690.06..136181.67 rows=472579 width=368) (actual time=986.580..7090.877 rows=472625 loops=1) Hash Cond: ((lignecomma0_1_.id)::text = (lignecomma0_.id)::text) -> Seq Scan on dm2_lignedocumentcommercialvente lignecomma0_1_ (cost=0.00..29881.18 rows=1431818 width=57) (actual time=14.401..2288.869 rows=1431818 loops=1) -> Hash (cost=15398.83..15398.83 rows=472579 width=311) (actual time=967.209..967.209 rows=472625 loops=1) -> Seq Scan on dm2_lignecommandevente lignecomma0_ (cost=0.00..15398.83 rows=472579 width=311) (actual time=18.154..662.185 rows=472625 loops=1) Filter: ((id)::text !~~ 'DefaultRecord_%'::text) Total runtime: 69612.191 ms ----------------------------------------------------------------------------------------------------------------------- We saw that both folowing queries give the same plan : - Static query with limit 500 removed explain analyze select * from dm2_lignecommandevente lignecomma0_ inner join dm2_lignedocumentcommercialvente lignecomma0_1_ on lignecomma0_.id=lignecomma0_1_.id inner join dm1_lignedocumentcommercial lignecomma0_2_ on lignecomma0_.id=lignecomma0_2_.id where (lignecomma0_.id not like 'DefaultRecord_%') and (lignecomma0_2_.dateFinValidite is null) order by coalescedate(lignecomma0_2_.dateCreationSysteme) desc - The bad prepared query PREPARE query(int) AS select * from dm2_lignecommandevente lignecomma0_ inner join dm2_lignedocumentcommercialvente lignecomma0_1_ on lignecomma0_.id=lignecomma0_1_.id inner join dm1_lignedocumentcommercial lignecomma0_2_ on lignecomma0_.id=lignecomma0_2_.id where (lignecomma0_.id not like 'DefaultRecord_%') and (lignecomma0_2_.dateFinValidite is null) order by coalescedate(lignecomma0_2_.dateCreationSysteme) desc limit $1; explain analyze execute query(500); We met the same behaviour with both : - PostgreSQL 8.4.8 on Windows 2008 (Prod) - PostgreSQL 8.4.8 and 8.4.17 on Windows 7 (Dev) I hope someone has any idea. *Ghislain ROUVIGNAC*