Queries joining views
Is there a trick to make this work a bit faster?
We have a number of views that join tables, and we have queries that
join those views. Some relatively large tables are involved.
We added indexes that match our query constraints as much as possible,
and that does work if we explicitly query the tables with all the
involved joins, instead of the views. However, if we query the views,
the planner starts using a filter instead of the desired index...
What we see basically is that adding one view to the query makes it go
from 12ms to 130ms...
zorgweb_solaris=> explain analyze SELECT
insrel.owner,insrel.rnumber,insrel.dnumber,insrel.snumber,insrel.dir,insrel.otype,insrel.number,medical_care_container.number,product.number
FROM mm_medical_care_container_table medical_care_container,mm_insrel
insrel,mm_product_table product WHERE
medical_care_container.number=558332 AND
(medical_care_container.number=insrel.dnumber AND
product.number=insrel.snumber AND insrel.dir<>1);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=114.23..203.24 rows=3 width=42) (actual
time=10.137..12.171 rows=1 loops=1)
-> Index Scan using mm_medical_care_container_table_pkey on
mm_medical_care_container_table medical_care_container (cost=0.00..5.64
rows=1 width=4) (actual time=0.048..0.052 rows=1 loops=1)
Index Cond: (number = 558332)
-> Nested Loop (cost=114.23..197.57 rows=3 width=38) (actual
time=10.077..12.106 rows=1 loops=1)
-> Merge Join (cost=114.23..186.13 rows=3 width=24) (actual
time=10.025..12.049 rows=1 loops=1)
Merge Cond: ("outer".number = "inner".snumber)
-> Index Scan using mm_product_table_pkey on
mm_product_table product (cost=0.00..67.90 rows=1571 width=4) (actual
time=0.025..9.460 rows=1571 loops=1)
-> Sort (cost=114.23..114.31 rows=30 width=20) (actual
time=0.144..0.145 rows=2 loops=1)
Sort Key: mm_insrel_table.snumber
-> Bitmap Heap Scan on mm_insrel_table
(cost=2.11..113.50 rows=30 width=20) (actual time=0.092..0.103 rows=2
loops=1)
Recheck Cond: ((dnumber = 558332) AND (dir
<> 1))
-> Bitmap Index Scan on
mm_insrel_dnumber_dir_not_one_idx (cost=0.00..2.11 rows=30 width=0)
(actual time=0.070..0.070 rows=2 loops=1)
Index Cond: (dnumber = 558332)
-> Index Scan using mm_object_pkey on mm_object
(cost=0.00..3.80 rows=1 width=18) (actual time=0.042..0.046 rows=1 loops=1)
Index Cond: ("outer".number = mm_object.number)
Total runtime: 12.765 ms
zorgweb_solaris=> explain analyze SELECT
insrel.rnumber,insrel.dnumber,insrel.snumber,insrel.dir,insrel.number,medical_care_container.number,product.number
FROM mm_medical_care_container medical_care_container,mm_insrel
insrel,mm_product product WHERE medical_care_container.number=558332 AND
(medical_care_container.number=insrel.dnumber AND
product.number=insrel.snumber AND insrel.dir<>1);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..172.69 rows=1 width=28) (actual
time=53.987..129.419 rows=1 loops=1)
-> Nested Loop (cost=0.00..168.88 rows=1 width=28) (actual
time=53.940..129.365 rows=1 loops=1)
-> Merge Join (cost=0.00..165.07 rows=1 width=28) (actual
time=53.890..129.310 rows=1 loops=1)
Merge Cond: ("outer".number = "inner".number)
-> Nested Loop (cost=0.00..2796.82 rows=30 width=28)
(actual time=44.088..117.487 rows=2 loops=1)
-> Nested Loop (cost=0.00..2682.38 rows=30
width=24) (actual time=44.034..117.375 rows=2 loops=1)
-> Index Scan using mm_insrel_full_idx on
mm_insrel_table (cost=0.00..2512.97 rows=30 width=20) (actual
time=43.975..117.246 rows=2 loops=1)
Index Cond: (dnumber = 558332)
-> Index Scan using
mm_medical_care_container_table_pkey on mm_medical_care_container_table
(cost=0.00..5.64 rows=1 width=4) (actual time=0.044..0.045 rows=1 loops=2)
Index Cond: (558332 = number)
-> Index Scan using mm_object_pkey on mm_object
(cost=0.00..3.80 rows=1 width=4) (actual time=0.041..0.043 rows=1 loops=2)
Index Cond: (mm_object.number = "outer".snumber)
-> Index Scan using mm_product_table_pkey on
mm_product_table (cost=0.00..67.90 rows=1571 width=4) (actual
time=0.023..9.443 rows=1571 loops=1)
-> Index Scan using mm_object_pkey on mm_object
(cost=0.00..3.80 rows=1 width=4) (actual time=0.040..0.042 rows=1 loops=1)
Index Cond: ("outer".number = mm_object.number)
-> Index Scan using mm_object_pkey on mm_object (cost=0.00..3.80
rows=1 width=4) (actual time=0.038..0.042 rows=1 loops=1)
Index Cond: (number = 558332)
Total runtime: 130.149 ms
zorgweb_solaris=> \d mm_insrel;
View "public.mm_insrel"
Column | Type | Modifiers
---------+---------+-----------
number | integer |
otype | integer |
owner | text |
snumber | integer |
dnumber | integer |
rnumber | integer |
dir | integer |
View definition:
SELECT mm_object.number, mm_object.otype, mm_object."owner",
mm_insrel_table.snumber, mm_insrel_table.dnumber,
mm_insrel_table.rnumber, mm_insrel_table.dir
FROM mm_insrel_table
JOIN mm_object USING (number);
zorgweb_solaris=> \d mm_medical_care_container
View "public.mm_medical_care_container"
Column | Type | Modifiers
--------+---------+-----------
number | integer |
otype | integer |
owner | text |
View definition:
SELECT mm_object.number, mm_object.otype, mm_object."owner"
FROM mm_medical_care_container_table
JOIN mm_object USING (number);
zorgweb_solaris=> \d mm_product
View "public.mm_product"
Column | Type | Modifiers
------------------------+---------+-----------
number | integer |
otype | integer |
owner | text |
created | bigint |
lastmodified | bigint |
start_time | bigint |
end_time | bigint |
title | text |
details | text |
only_collectively | boolean |
term_of_notice | text |
max_number_paying_kids | integer |
contract_term | text |
advance_declarations | text |
free_care_choice | text |
export_to_rivm | boolean |
export_to_kwiz | boolean |
export_to_independer | boolean |
show_in_frontend | boolean |
path | text |
type_notes | text |
View definition:
SELECT mm_object.number, mm_object.otype, mm_object."owner",
mm_product_table.created, mm_product_table.lastmodified,
mm_product_table.start_time, mm_product_table.end_time, mm_p
roduct_table.title, mm_product_table.details,
mm_product_table.only_collectively, mm_product_table.term_of_notice,
mm_product_table.max_number_paying_kids, mm_product_table.contra
ct_term, mm_product_table.advance_declarations,
mm_product_table.free_care_choice, mm_product_table.export_to_rivm,
mm_product_table.export_to_kwiz, mm_product_table.export_to_ind
epender, mm_product_table.show_in_frontend, mm_product_table.path,
mm_product_table.type_notes
FROM mm_product_table
JOIN mm_object USING (number);
--
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 //
Alban Hertroys wrote:
We have a number of views that join tables, and we have queries that
join those views. Some relatively large tables are involved.
We added indexes that match our query constraints as much as possible,
and that does work if we explicitly query the tables with all the
involved joins, instead of the views. However, if we query the views,
the planner starts using a filter instead of the desired index...
Anecdotally, I had a situation recently where I got different plans
depending on whether I queried a join of a view against itself, or
"macro expanded" the view by hand. I was =very= surprised at this.
- John D. Burger
MITRE
"John D. Burger" <john@mitre.org> writes:
Anecdotally, I had a situation recently where I got different plans
depending on whether I queried a join of a view against itself, or
"macro expanded" the view by hand. I was =very= surprised at this.
Me too, at least if you didn't do any hand optimization but just stuck
the view definition in as a sub-select. Can you provide a reproducible
case?
regards, tom lane
Alban Hertroys <alban@magproductions.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
Tom Lane wrote:
Anecdotally, I had a situation recently where I got different plans
depending on whether I queried a join of a view against itself, or
"macro expanded" the view by hand. I was =very= surprised at this.Me too, at least if you didn't do any hand optimization but just stuck
the view definition in as a sub-select. Can you provide a reproducible
case?
No surprise, I cannot reproduce this, especially since the DDL has
evolved since then. But you are almost certainly right, my "macro
expansion" must have done something more than simply dropping in the
view definition as is. Presumably something minor (to me) but a
show-stopper for the planner.
Sorry for the distraction. :(
- John D. Burger
MITRE