pg_query_params and partial index

Question

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 !!

Answer 1

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:

CREATE INDEX idx_id_status ON mytable (id, status);

This works in version 8.2 and 9.1 as well.