Re: Queries joining views - Mailing list pgsql-general

Alban Hertroys wrote:
> Tom Lane wrote:
> I'm thinking that removing the indexes it's erroneously using now could
> help performance, as it can no longer use that index. It may however
> pick the primary key index (likely), or - if we remove even that one - a
> sequential scan... Experimenting will answer that.

Well, look at the attachhed explain output of:

zorgweb_solaris=> BEGIN;
DROP INDEX mm_insrel_full_idx; DROP INDEX mm_insrel_relation_idx;
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);
ROLLBACK;

Only 13ms as opposed to 130-ish :)

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

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=118.34..211.13 rows=1 width=28) (actual time=10.943..12.988 rows=1 loops=1)
   ->  Nested Loop  (cost=118.34..205.49 rows=1 width=28) (actual time=10.903..12.942 rows=1 loops=1)
         ->  Nested Loop  (cost=118.34..201.67 rows=1 width=28) (actual time=10.857..12.891 rows=1 loops=1)
               ->  Merge Join  (cost=118.34..190.23 rows=3 width=28) (actual time=10.807..12.837 rows=1 loops=1)
                     Merge Cond: ("outer".number = "inner".snumber)
                     ->  Index Scan using mm_product_table_pkey on mm_product_table  (cost=0.00..67.90 rows=1571
width=4)(actual time=0.040..9.542 rows=1571 loops=1) 
                     ->  Sort  (cost=118.34..118.41 rows=30 width=24) (actual time=0.867..0.868 rows=2 loops=1)
                           Sort Key: mm_insrel_table.snumber
                           ->  Nested Loop  (cost=2.11..117.60 rows=30 width=24) (actual time=0.804..0.827 rows=2
loops=1)
                                 ->  Index Scan using mm_object_pkey on mm_object  (cost=0.00..3.80 rows=1 width=4)
(actualtime=0.055..0.061 rows=1 loops=1) 
                                       Index Cond: (number = 558332)
                                 ->  Bitmap Heap Scan on mm_insrel_table  (cost=2.11..113.50 rows=30 width=20) (actual
time=0.723..0.734rows=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=30width=0) (actual time=0.705..0.705 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=4) (actual
time=0.040..0.043rows=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.040
rows=1loops=1) 
               Index Cond: ("outer".number = mm_object.number)
   ->  Index Scan using mm_medical_care_container_table_pkey on mm_medical_care_container_table  (cost=0.00..5.64
rows=1width=4) (actual time=0.033..0.036 rows=1 loops=1) 
         Index Cond: (558332 = number)
 Total runtime: 13.799 ms
(22 rows)