Re: Queries joining views

Tom Lane wrote:
> DelGurth <delgurth@gmail.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@magproductions.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 //