Re: Queries joining views

>
> Alban Hertroys <alban ( at ) magproductions ( dot ) nl> writes:
> > Is there a trick to make this work a bit faster?
>
> Have you really shown us the right queries for those explain results?
> I don't see where the second plan is testing "dir <> 1" at all.
> It looks like the first one is faster because it's using a partial
> index that has predicate dir <> 1, while the second one is using
> a much larger full index. But I don't see where the second plan
> is applying that restriction, so I wonder if you forgot it in the
> query.
>
> regards, tom lane

He has really shown the right queries. But I see the table definition if
mm_insrel_table (including the indexes) is not in the e-mail, so you don't
see why the dir <> 1 is not in the query plan. Here is the table definition,
with the indexes. As you can see we tried some indexes, to see if we could
get the queries on the views to become faster.

zorgweb_solaris=> \d mm_insrel_table
Table "public.mm_insrel_table"
Column | Type | Modifiers
---------+---------+-----------
number | integer | not null
snumber | integer | not null
dnumber | integer | not null
rnumber | integer | not null
dir | integer |
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)
Foreign-key constraints:
"mm_insrel_table_dnumber_fkey" FOREIGN KEY (dnumber) REFERENCES
mm_object(number)
"mm_insrel_table_rnumber_fkey" FOREIGN KEY (rnumber) REFERENCES
mm_object(number)
"mm_insrel_table_snumber_fkey" FOREIGN KEY (snumber) REFERENCES
mm_object(number)

I hope this explains you why the dir <> 1 is not in the view query. Why the
other query plan thinks it needs to recheck the condition is not clear to
me, but I'm not an expert on PostgreSQL query plans.

Regards,
Wessel van Norel