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@xxxxxxxxxxxxxxxxx
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) (actual time=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.734 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.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.043 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.040 rows=1 loops=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=1 width=4) (actual time=0.033..0.036 rows=1 loops=1)
Index Cond: (558332 = number)
Total runtime: 13.799 ms
(22 rows)
