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