Search Postgresql Archives

Re: Queries joining views

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux