pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: JmH <jason.height@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Good/Bad query plans based on text criteria
Date: Thu, 15 Apr 2010 11:15:45 +0930
Message-ID: <q2naba242eb1004141845je43ba462of5fddfaa8b03d426@mail.gmail.com> (raw)

Hello,

I am struggling to understand why for certain criteria that i supply for a
query alters the the query plan. In my "good" case i can see that an index
is used, in my bad case where i only change the text value of the criteria,
but not the criteria itslef (ie change/add the conditions) a hbitmap heap
scan of the table is performed.

Refer attached Good/Bad query plans.

The basic query is:

SELECT * FROM highrate_log_entry
WHERE
test_seq_number > 26668670
and udc = '2424'
AND (test_signal_number = 'D2030'
)
ORDER BY test_seq_number LIMIT 11

test_seq_number is the pk and is generated by a sequence.

The D2030 is the only thing that i vary between good/bad runs. The issue is
possibly related to the  data spead is for the test-signal_number is not
uniform, but there does not appear to be that much difference in difference
between the first sequence number and the last sequence number (to achieve
the 11 results), when compared between the  test_seq_number that yield good
or bad results.

I dont believe that the issue is to do with re-writing the query, but how
the planner chooses its path.

I am using Postgres 8.4 on windows with default postgres.conf. I have tried
changing(increasing) shared_buffers, work_mem and effective_cache_size
without success.

Any suggestions would be appreciated.

Thanks

Jason

Attachments:

GoodPlan.txt (text/plain, 1.8K) download explain analyze SELECT * FROM highrate_log_entry WHERE test_seq_number > 26668670 and udc = '2424' AND (test_signal_number = 'D2030' ) ORDER BY test_seq_number LIMIT 11 QUERY PLAN Limit (cost=6694.14..6694.17 rows=11 width=61) (actual time=2.859..2.881 rows=11 loops=1) -> Sort (cost=6694.14..6695.82 rows=672 width=61) (actual time=2.856..2.863 rows=11 loops=1) Sort Key: public.highrate_log_entry.test_seq_number Sort Method: top-N heapsort Memory: 18kB -> Result (cost=0.00..6679.16 rows=672 width=61) (actual time=0.973..2.484 rows=254 loops=1) -> Append (cost=0.00..6679.16 rows=672 width=61) (actual time=0.970..2.046 rows=254 loops=1) -> Index Scan using high_rate_index on highrate_log_entry (cost=0.00..8.27 rows=1 width=145) (actual time=0.009..0.009 rows=0 loops=1) Index Cond: ((udc = 2424::numeric) AND ((test_signal_number)::text = 'D2030'::text)) Filter: (test_seq_number > 26668670::numeric) -> Index Scan using highrate_log_entry_2424_2009_pkey on highrate_log_entry_2424_2009 highrate_log_entry (cost=0.00..3734.01 rows=1 width=60) (actual time=0.020..0.020 rows=0 loops=1) Index Cond: ((udc = 2424::numeric) AND (test_seq_number > 26668670::numeric)) Filter: ((test_signal_number)::text = 'D2030'::text) -> Index Scan using highrate_log_entry_2424_2010_udc_key on highrate_log_entry_2424_2010 highrate_log_entry (cost=0.00..2936.88 rows=670 width=61) (actual time=0.935..1.620 rows=254 loops=1) Index Cond: ((udc = 2424::numeric) AND ((test_signal_number)::text = 'D2030'::text)) Filter: (test_seq_number > 26668670::numeric) Total runtime: 2.949 ms
BadPlan.txt (text/plain, 2.1K) download explain analyze SELECT * FROM highrate_log_entry WHERE test_seq_number > 26668670 and udc = '2424' AND (test_signal_number = 'D2048' ) ORDER BY test_seq_number LIMIT 11 QUERY PLAN Limit (cost=107017.91..107017.94 rows=11 width=61) (actual time=2102.129..2102.152 rows=11 loops=1) -> Sort (cost=107017.91..107112.96 rows=38021 width=61) (actual time=2102.126..2102.133 rows=11 loops=1) Sort Key: public.highrate_log_entry.test_seq_number Sort Method: top-N heapsort Memory: 18kB -> Result (cost=0.00..106170.15 rows=38021 width=61) (actual time=1428.960..2066.815 rows=26497 loops=1) -> Append (cost=0.00..106170.15 rows=38021 width=61) (actual time=1428.957..2024.959 rows=26497 loops=1) -> Index Scan using high_rate_index on highrate_log_entry (cost=0.00..8.27 rows=1 width=145) (actual time=0.006..0.006 rows=0 loops=1) Index Cond: ((udc = 2424::numeric) AND ((test_signal_number)::text = 'D2048'::text)) Filter: (test_seq_number > 26668670::numeric) -> Index Scan using highrate_log_entry_2424_2009_pkey on highrate_log_entry_2424_2009 highrate_log_entry (cost=0.00..3734.01 rows=22 width=60) (actual time=0.017..0.017 rows=0 loops=1) Index Cond: ((udc = 2424::numeric) AND (test_seq_number > 26668670::numeric)) Filter: ((test_signal_number)::text = 'D2048'::text) -> Bitmap Heap Scan on highrate_log_entry_2424_2010 highrate_log_entry (cost=2600.42..102427.87 rows=37998 width=61) (actual time=1428.928..1987.050 rows=26497 loops=1) Recheck Cond: ((udc = 2424::numeric) AND ((test_signal_number)::text = 'D2048'::text)) Filter: (test_seq_number > 26668670::numeric) -> Bitmap Index Scan on highrate_log_entry_2424_2010_udc_key (cost=0.00..2590.92 rows=93311 width=0) (actual time=370.824..370.824 rows=90282 loops=1) Index Cond: ((udc = 2424::numeric) AND ((test_signal_number)::text = 'D2048'::text)) Total runtime: 2102.291 ms

view thread (2+ messages)  latest in thread

Message-ID: <q2naba242eb1004141845je43ba462of5fddfaa8b03d426@mail.gmail.com>
Permalink:  ../q2naba242eb1004141845je43ba462of5fddfaa8b03d426@mail.gmail.com/
Also on:    postgresql.org/message-id/q2naba242eb1004141845je43ba462of5fddfaa8b03d426@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: jason.height@gmail.com
  Subject: Re: Good/Bad query plans based on text criteria
  In-Reply-To: <q2naba242eb1004141845je43ba462of5fddfaa8b03d426@mail.gmail.com>
  References: <q2naba242eb1004141845je43ba462of5fddfaa8b03d426@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox