Thread: ...

Please, help me.

For example, I have a table
> create table tab1
> (field1 int4 primary key, field2 int4, field3 int4);

.. and I want to make the next query:
> select field1, field2, field3
> from tab1
> where field2=<some value>
> order by field3;

What indexes I have to make?
Either:
> create index idx1 on tab1 (field2, field3);
Or:
> create index idx1 on tab1 (field2);
> create index idx2 on tab1 (field3);
Or something else?

Pavel Mamin,
Network Programmer,
SkyNet Co. ISP, Yekaterinburg, Russia

Pavel Mamin <pm@sky.ru> writes:
> .. and I want to make the next query:
>> select field1, field2, field3
>> from tab1
>> where field2=<some value>
>> order by field3;

> What indexes I have to make?

You don't *have* to make any indexes.  But if you do a lot of queries
that use field2 in the WHERE condition, an index on field2 would make
them faster.

I don't think an index on field3 would be helpful for this query.
Unless a very large fraction of the rows in tab1 have the same
field2 value you are selecting for, it's going to be fastest to
pull out the matching rows using a field2 index and then sort them
on field3.  Scanning the table with a field3 index would deliver the
result already sorted --- but you'd have to visit every row in order
to see whether it matched the WHERE clause, so that'd be a loser.

In theory an index on (field2, field3) would be the perfect thing for
this particular query, but Postgres isn't actually smart enough to
realize that it could use such an index and not have to sort on
field3.  Anyway, such an index would be so specialized that it probably
wouldn't be worth the maintenance costs...
        regards, tom lane