Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <h2>Server configuration</h2> <p>That much is clear: the default settings are very conservative and intended to work for small installations with limited resources out of the box. For a dedicated DB server, some default settings are just inadequate. You have to tune your settings.</p> <p>To start with, if you have enough RAM to cache all or most of your DB, set <code>random_page_cost</code> drastically lower. And increase the relative cost of CPU operations. Something like (this is pure guesswork!):</p> <pre> seq_page_cost: 1 random_page_cost: 1.2 cpu_tuple_cost: .02 cpu_index_tuple_cost: .02 cpu_operator_cost: .005 </pre> <p>And <code>effective_cache_size</code> is regularly <em>much too low</em>. For a <em>dedicated</em> DB server this can be as high as three quarters of your total RAM.</p> <p>@Craig has assembled a long list of advice for performance tuning:<br> <a href="https://stackoverflow.com/questions/9407442/optimise-postgresql-for-fast-testing/9407940#9407940">Optimise PostgreSQL for fast testing</a></p> <p><a href="https://wiki.postgresql.org/wiki/Performance_Optimization" rel="nofollow noreferrer">The Postgres Wiki has even more.</a></p> <h2>Query</h2> <p>Too many redundant parentheses, too hard to read. Use table aliases and format before trying to debug - much less presenting to the general public. After untangling:</p> <pre><code>SELECT count(*) AS count_all FROM events e JOIN tickets t ON t.event_id = e.id JOIN access_rights a ON a.ticket_id = t.id LEFT JOIN departments d ON d.id = a.target_id AND a.target_type = 'Department' WHERE e.activity = 'f' AND (e.canceled_at IS NULL OR e.canceled_at &gt; '2013-10-29 23:11:37') AND (t.hold_until IS NULL OR t.hold_until &lt;= '2013-10-29') AND t.company_id = 173; AND a.section = 'shop' AND (a.target_type = 'Company' AND a.target_id = 173 OR a.target_type = 'User' AND a.target_id = 11654 OR a.target_type = 'UserGroup' AND a.target_id IN (126) OR d.lft &lt;= 7 AND d.rgt &gt;= 8 -- a.target_type = 'Department' is redundant ) AND (a.frozen_activation = 'active' OR a.active_on &lt;= '2013-10-29' AND (a.inactive_on IS NULL OR a.inactive_on &gt; '2013-10-29') AND a.frozen_activation IS NULL ) </code></pre> <h3>Major points</h3> <ul> <li><p>Redundant: <code>AND a.active_on IS NOT NULL</code>, since you also have <code>AND a.active_on &lt;= '2013-10-29'</code></p></li> <li><p><code>AND a.target_id IN ('126')</code> should be <code>AND a.target_id = 126</code> or at least <code>AND a.target_id IN (126)</code> (numeric constant).</p></li> <li><p><code>a.target_type = 'Department'</code> is redundant, since its already in the <code>LEFT JOIN</code></p></li> <li><p><code>AND a.section = 'shop'</code> is redundant many times.</p></li> <li><p><code>target_type_id</code> should most probably be an <a href="http://www.postgresql.org/docs/current/interactive/datatype-enum.html" rel="nofollow noreferrer"><code>enum</code></a> or <code>integer</code> referencing a table <code>target_type</code> instead of a <code>varchar(255)</code>.</p> <pre><code>CREATE TABLE access_rights ( ... ,target_type_id integer NOT NULL REFERENCES target_type(target_type_id) ... ); </code></pre> <p>Similar for <code>a.frozen_activation</code> and <code>a.section</code>.</p></li> </ul> <p>That would also make the index I am going to propose more effective.</p> <h2>Indices</h2> <p>Add a few multicolumn / partial indices. Tailor yourself, I don't know cardinalities and data distribution. Note the <code>DESC</code> clauses in strategic places.</p> <pre><code>CREATE INDEX e_idx ON events (company_id, event_id, hold_until) WHERE activity = FALSE; CREATE INDEX t_idx ON tickets (company_id, event_id, hold_until DESC); CREATE INDEX a_idx1 ON access_rights (target_type_id, target_id) WHERE section = 'shop'; CREATE INDEX a_idx2 ON access_rights (frozen_activation, active_on DESC, inactive_on) WHERE section = 'shop'; CREATE INDEX d_idx ON departments (target_type, lft DESC, rgt); </code></pre> <p>Other than that you only need the primary keys and indices on foreign keys. All other indices you display would then be useless for this query. Delete some if they are not needed elsewhere.</p> <p>For details on how to tailor these indices consider the related answer on dba.SE:</p> <ul> <li><a href="https://dba.stackexchange.com/questions/33196/multicolumn-index-and-performance/33220#33220">Multicolumn index and performance</a> </li> <li><a href="https://dba.stackexchange.com/questions/27481/is-a-composite-index-also-good-for-queries-on-the-first-field/27493#27493">Is a composite index also good for queries on the first field?</a></li> </ul>
    singulars
    1. This table or related slice is empty.
    1. POquery planner inaccurate choosing nested join
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USErwin Brandstetter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POquery planner inaccurate choosing nested join
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks for your help on this. Why does caching most of your database make CPU operations more expensive?
      singulars
      1. PO
      1. USjvans
    2. CO@jvans: It doesn't. Page lookups become cheaper. Those are all just relative numbers without absolute meaning. CPU operations become *relatively* more expensive.
      singulars
      1. PO
      1. USErwin Brandstetter
    3. COAwesome that makes a lot of sense. So is it fair to say that effective_cache_size is inversely proportional to cpu cost estimates? Whenever you increase the cache the cpu cost become relatively more expensive and you should increase the cpu_* cost estimates?
      singulars
      1. PO
      1. USjvans
 

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