Re: Can't get planner to use multicolumn index on large

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