Using PHP 5.3.10
against a (quite old) PostgreSQL 8.2.23
database.
I am using a query like this :
SELECT * FROM mytable WHERE status = 1 AND id = 123456;
I created a partial index to improve performance :
CREATE INDEX i1 ON mytable (id) WHERE status = 1;
The problem is, when I use pg_query_params
, it seems that the partial index is not used (and the query is very slow) :
pg_query_params('SELECT * FROM mytable WHERE status = $1 AND id = $2', array(1,123456));
I test the same PHP code on a PostgreSLQ 9.1
database and it seems to work well (index used). Unfortunately, I can not upgrade to 9.1
at the moment. The faster workaround for me seems not to use partial index...
Is there any known limitation with pg_query_params
and partial index over old versions of PG ?
Do I have a workaround other than avoiding partial index when using pg_query_params
?
EDIT :
Here is the execution plan, on 8.2.23 server and 9.1.2 server. The query executed (by PHP) was :
EXPLAIN ANALYZE SELECT * FROM mytable WHERE status = $1 AND id = $2
BUT, strange behaviour, when really executing the query (I mean removing "EXPLAIN ANALYZE") on PG 9.1 using pg_prepare, the query does not seem to use index (symptom : very slow query, a few seconds... like a seq scan!?).
PG 8.2, using pg_query_params :
Seq Scan on mytable (cost=0.00..289976.55 rows=1 width=6) (actual time=851.956..3112.038 rows=1 loops=1)
PG 8.2, using pg_prepare + pg_execute :
Seq Scan on mytable (cost=0.00..289976.55 rows=1 width=6) (actual time=399.486..1595.102 rows=1 loops=1)
PG 9.1, using pg_query_params :
Index Scan using i1 on mytable (cost=0.00..9.61 rows=1 width=6) (actual time=0.046..0.047 rows=1 loops=1)
PG 9.1, using pg_prepare + pg_execute :
Index Scan using i1 on mytable (cost=0.00..9.61 rows=1 width=6) (actual time=0.043..0.043 rows=1 loops=1)
=> but as I said, the real duration of this last pg_execute on PG 9.1 is 1'860 ms !!
The problem with a prepared statement is that during preparing, it has no idea what values could come in later on. The index conditions says WHERE status = 1, but will the query has a "status=1" value? Hard to predict.
For a query like this, using a prepared statement, an index on both conditions could be the best performance option:
This works in version 8.2 and 9.1 as well.