Queries joining views - Mailing list pgsql-general

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 //