Re: Queries joining views

Tom Lane wrote:
> 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?

Here they are:

relname | relpages | reltuples
-----------------------------------+----------+-----------
mm_insrel_dir_not_one_idx | 899 | 323628
mm_insrel_dnumber_dir_not_one_idx | 899 | 323628
mm_insrel_table_pkey | 1237 | 323628
mm_insrel_relation_idx | 1849 | 323628
mm_insrel_full_idx | 1260 | 323628

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //