Using PG 9.0 and given 2 queries (messageq_current is a view on the messageq_table):
select entity_id from messageq_current
where entity_id = 123456;
select entity_id from messageq_current
where incoming = true
and inactive = false
and staff_ty = 2
and staff_id = 2
order by entity_id desc
limit 1;
and 2 indexes (there are 15 indexes in total but they are left out here for brevity):
messageq1:
CREATE INDEX messageq1
ON messageq_table
USING btree
(entity_id);
And messageq4:
CREATE INDEX messageq4
ON messageq_table
USING btree
(inactive, staff_ty, staff_id, incoming, tran_dt);
Of course a lot of detail is missing (full schema of table, all the other indexes) but with "inactive" a boolean column I suspect selectivity might not be too good here and so having it as a first column in a covering index is at least questionable. If query 2 is frequent you might also want to consider creating a partial index only on (staff_ty, staff_id) with filtering criteria on incoming and active as present in query 2.
Btw, why don't you formulate query 2 as max query?
select max(entity_id) as entity_id
from messageq_current
where incoming = true
and inactive = false
and staff_ty = 2
and staff_id = 2;
With the messageq1 index present, query 1 is very quick (0.094ms) and query 2 is very slow (241.515ms).
If I remove messageq1 then query 2 uses messageq4 and is very quick (0.098ms) but then query 1 must use a different index and is therefore slower (> 5ms).
So, to the Query plans:
Of which query? Shouldn't there be four plans in total? I'd post plans here:
http://explain.depesz.com/
With messageq1:
"Limit (cost=0.00..2670.50 rows=1 width=4) (actual time=241.481..241.481 rows=0 loops=1)"
" Output: messageq_table.entity_id"
" Buffers: shared hit=32 read=18870 written=12"
" -> Index Scan Backward using messageq1 on prac_live_10112.messageq_table (cost=0.00..66762.53 rows=25 width=4) (actual time=241.479..241.479 rows=0 loops=1)"
" Output: messageq_table.entity_id"
" Filter: (messageq_table.incoming AND (NOT messageq_table.inactive) AND (messageq_table.staff_ty = 2) AND (messageq_table.staff_id = 2) AND (aud_status_to_flag(messageq_table.aud_status) = 1))"
" Buffers: shared hit=32 read=18870 written=12"
"Total runtime: 241.515 ms"
Without messageq1:
"Limit (cost=12534.45..12534.45 rows=1 width=4) (actual time=0.055..0.055 rows=0 loops=1)"
" Output: messageq_table.entity_id"
" Buffers: shared read=3"
" -> Sort (cost=12534.45..12534.51 rows=25 width=4) (actual time=0.054..0.054 rows=0 loops=1)"
" Output: messageq_table.entity_id"
" Sort Key: messageq_table.entity_id"
" Sort Method: quicksort Memory: 17kB"
" -> Bitmap Heap Scan on prac_live_10112.messageq_table (cost=174.09..12534.32 rows=25 width=4) (actual time=0.043..0.043 rows=0 loops=1)"
" Output: messageq_table.entity_id"
" Recheck Cond: ((messageq_table.staff_ty = 2) AND (messageq_table.staff_id = 2))"
" Filter: (messageq_table.incoming AND (NOT messageq_table.inactive) AND (aud_status_to_flag(messageq_table.aud_status) = 1))"
" Buffers: shared read=3"
" -> Bitmap Index Scan on messageq4 (cost=0.00..174.08 rows=4920 width=0) (actual time=0.040..0.040 rows=0 loops=1)"
" Index Cond: ((messageq_table.inactive = false) AND (messageq_table.staff_ty = 2) AND (messageq_table.staff_id = 2) AND (messageq_table.incoming = true))"
" Buffers: shared read=3"
"Total runtime: 0.098 ms"
Clearly the statistics are off somehow but I really don’t know where to start.
Any help you can give me would be very much appreciated.
Kind regards
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
