Hmph ... it certainly appears to be choosing the wrong index in the
second case. I wonder why --- can you show the relpages and reltuples
stats from pg_class for these indexes?
I'm personally not aware how to do that, perhaps Alban will (tell me how to) do that tomorrow.
It might be interesting also to examine the output of just
explain select * from mm_insrel_table where dnumber=558332 and dir<>1
with different subsets of these indexes in place. I'd like to see what
it's deriving as the cost estimates for these indexes. If you can get
an EXPLAIN selecting each one of the indexes, that would help diagnose
what's going on.
Ok. Did that (with your trick, thanks!). The output is attached to this e-mail. The script I used to drop the indexes dropped them in the order it was using them (partially by accident, partially because I assumed it would work in that order).
I'm not sure if you want to see more permutations, if so please tell me.
BTW, what PG version is this exactly?
Our PG version is the version downloadable from http://www.sunfreeware.com/programlistsparc10.html#postgresql , so 8.0.1 for solaris sparc.
(it's a sun T2000 test machine).
regards, tom lane
Regards,
Wessel van Norel
begin;
explain analyze select * from mm_insrel_table where dnumber=558332 and dir<>1;
rollback;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on mm_insrel_table (cost=2.11..113.50 rows=30 width=20) (actual time=0.089..0.096 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)
Total runtime: 0.238 ms
(5 rows)
begin;
drop index mm_insrel_dnumber_dir_not_one_idx;
explain analyze select * from mm_insrel_table where dnumber=558332 and dir<>1;
rollback;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on mm_insrel_table (cost=2.11..113.50 rows=30 width=20) (actual time=0.084..0.091 rows=2 loops=1)
Recheck Cond: ((dnumber = 558332) AND (dir <> 1))
-> Bitmap Index Scan on mm_insrel_dir_not_one_idx (cost=0.00..2.11 rows=30 width=0) (actual time=0.067..0.067 rows=2 loops=1)
Index Cond: (dnumber = 558332)
Total runtime: 0.226 ms
(5 rows)
begin;
drop index mm_insrel_dnumber_dir_not_one_idx;
drop index mm_insrel_dir_not_one_idx;
explain analyze select * from mm_insrel_table where dnumber=558332 and dir<>1;
rollback;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using mm_insrel_full_idx on mm_insrel_table (cost=0.00..2533.97 rows=30 width=20) (actual time=43.965..162.962 rows=2 loops=1)
Index Cond: (dnumber = 558332)
Total runtime: 163.085 ms
(3 rows)
begin;
drop index mm_insrel_dnumber_dir_not_one_idx;
drop index mm_insrel_dir_not_one_idx;
drop index mm_insrel_full_idx;
explain analyze select * from mm_insrel_table where dnumber=558332 and dir<>1;
rollback;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using mm_insrel_relation_idx on mm_insrel_table (cost=0.00..3102.97 rows=30 width=20) (actual time=44.608..164.894 rows=2 loops=1)
Index Cond: (dnumber = 558332)
Filter: (dir <> 1)
Total runtime: 165.025 ms
(4 rows)
begin;
drop index mm_insrel_dnumber_dir_not_one_idx;
drop index mm_insrel_dir_not_one_idx;
drop index mm_insrel_full_idx;
drop index mm_insrel_relation_idx;
explain analyze select * from mm_insrel_table where dnumber=558332 and dir<>1;
rollback;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on mm_insrel_table (cost=0.00..8160.42 rows=30 width=20) (actual time=855.286..884.919 rows=2 loops=1)
Filter: ((dnumber = 558332) AND (dir <> 1))
Total runtime: 885.042 ms
(3 rows)
