On Thu, Aug 2, 2012 at 4:54 PM, Russell Keane <russell.ke...@inps.co.uk>wrote:
> ** ** > > 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/
