Databases
StackOverflow2013
Postsdbo
POWhy does PostgresQL query performance drop over time, but restored when rebuilding index
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy does PostgresQL query performance drop over time, but restored when rebuilding index
    primarykey
    data
    text
    <p>According to this <a href="http://www.postgresql.org/docs/current/static/indexes-examine.html" rel="nofollow noreferrer">page</a> in the manual, <code>indexes don't need to be maintained</code>. However, we are running with a PostgresQL table that has a continuous rate of <code>updates</code>, <code>deletes</code> and <code>inserts</code> that over time (a few days) sees a significant query degradation. If we delete and recreate the index, query performance is restored.</p> <p>We are using out of the box settings.<br> The table in our test is currently starting out empty and grows to half a million rows. It has a fairly large row (lots of text fields).</p> <p>We are <code>searching based of an index, not the primary key</code> (I've confirmed the index is being used, at least under normal conditions)</p> <p>The table is being used as a persistent store for a single process. Using PostgresQL on Windows with a Java client.</p> <p>I'm willing to give up <code>insert and update performance</code> to keep up the query performance.</p> <p>We are considering rearchitecting the application so that data is spread across various dynamic tables in a manner that allows us to drop and rebuild indexes periodically without impacting the application. However, as always, there is a time crunch to get this to work and I suspect we are missing something basic in our configuration or usage.</p> <p>We have considered <code>forcing vacuuming</code> and <code>rebuild to run at certain times</code>, but I suspect the <code>locking period for such an action would cause our query to block</code>. This may be an option, but there are some real-time (windows of 3-5 seconds) implications that require other changes in our code.</p> <p><strong>Additional information:</strong> Table and index</p> <pre><code>CREATE TABLE icl_contacts ( id bigint NOT NULL, campaignfqname character varying(255) NOT NULL, currentstate character(16) NOT NULL, xmlscheduledtime character(23) NOT NULL, ... 25 or so other fields. Most of them fixed or varying character fiel ... CONSTRAINT icl_contacts_pkey PRIMARY KEY (id) ) WITH (OIDS=FALSE); ALTER TABLE icl_contacts OWNER TO postgres; CREATE INDEX icl_contacts_idx ON icl_contacts USING btree (xmlscheduledtime, currentstate, campaignfqname); </code></pre> <p>Analyze:</p> <pre><code>Limit (cost=0.00..3792.10 rows=750 width=32) (actual time=48.922..59.601 rows=750 loops=1) -&gt; Index Scan using icl_contacts_idx on icl_contacts (cost=0.00..934580.47 rows=184841 width=32) (actual time=48.909..55.961 rows=750 loops=1) Index Cond: ((xmlscheduledtime &lt; '2010-05-20T13:00:00.000'::bpchar) AND (currentstate = 'SCHEDULED'::bpchar) AND ((campaignfqname)::text = '.main.ee45692a-6113-43cb-9257-7b6bf65f0c3e'::text)) </code></pre> <p>And, yes, I am aware there there are a variety of things <code>we could do to normalize and improve the design of this table</code>. Some of these options may be available to us.</p> <p><strong>My focus in this question is</strong> about understanding <code>how PostgresQL is managing the index and query over time (understand why, not just fix)</code>. If it were to be done over or significantly refactored, there would be a lot of changes.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSkyWalker
    1. USJim Rush
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhy does PostgresQL query performance drop over time, but restored when rebuilding index
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhy does PostgresQL query performance drop over time, but restored when rebuilding index
      1. USbdk
      1. VTFavorite
    3. VO
      singulars
      1. POWhy does PostgresQL query performance drop over time, but restored when rebuilding index
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat version do you use? When you abuse VACUUM FULL, you're bloating your indexes and you have to do a REINDEX. And forget the out of the box settings, that's not going to perform. You have to configure your database to tell it how to use memory, do auto_vacuum, etc. No configuration, no performance.
      singulars
      1. POWhy does PostgresQL query performance drop over time, but restored when rebuilding index
      1. USFrank Heikens
    2. COWhat version are you using? Do you have autovacuum running? Are you logging autovacuum calls so you can see how often it is actually cleaning up records for re-use?
      singulars
      1. POWhy does PostgresQL query performance drop over time, but restored when rebuilding index
      1. USMatthew Wood
    3. COAnd provide proper execution plan (created using explain analyze) for those queries.
      singulars
      1. POWhy does PostgresQL query performance drop over time, but restored when rebuilding index
      1. USSzymon Lipiński
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload