Re: Can't get planner to use multicolumn index on large - Mailing list pgsql-general

Ulrich Meis wrote:
> CREATE TABLE data.question_result (
>     id                  bigserial   PRIMARY KEY,
>     trial_id            bigint      NOT NULL REFERENCES data.trial(id),
>     question_id         bigint      REFERENCES content.question(id),
> <two more columns>,
> );

> mydb=# explain analyze select * from data.question_result where trial_id=1
> and question_id=2;

This is a well-known optimizer deficiency. You need to single-quote the
numeric literals or cast them to the type of the column, or else you
won't get index scans for non-int4 columns. In other words:

explain analyze select * from data.question_result where trial_id='1'
and question_id='2'

This is fixed in 8.0

-Neil