Re: Queries joining views

DelGurth <delgurth(at)gmail(dot)com> writes:
> As you can see we tried some indexes, to see if we could
> get the queries on the views to become faster.

> Indexes:
> "mm_insrel_table_pkey" PRIMARY KEY, btree (number)
> "mm_insrel_dir_not_one_idx" btree (dnumber, snumber) WHERE dir <> 1
> "mm_insrel_dnumber_dir_not_one_idx" btree (dnumber) WHERE dir <> 1
> "mm_insrel_full_idx" btree (snumber, dnumber, rnumber, dir) WHERE dir <>
> 1
> "mm_insrel_relation_idx" btree (snumber, dnumber, rnumber)

Hmph ... it certainly appears to be choosing the wrong index in the
second case. I wonder why --- can you show the relpages and reltuples
stats from pg_class for these indexes?

It might be interesting also to examine the output of just

explain select * from mm_insrel_table where dnumber=558332 and dir<>1

with different subsets of these indexes in place. I'd like to see what
it's deriving as the cost estimates for these indexes. If you can get
an EXPLAIN selecting each one of the indexes, that would help diagnose
what's going on.

Dunno if you know this trick already, but it's possible to experiment
with different index subsets without physically dropping and recreating
the indexes. Try

begin;
drop [unwanted indexes]
explain ...
rollback;

This will hold exclusive lock on the table until you rollback, so if
it's a production database you want to be quick about it --- maybe put
the whole thing in a SQL script. But it sure beats rebuilding indexes.

BTW, what PG version is this exactly?

regards, tom lane