StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The output of EXPLAIN ANALYZE is identical up to the sort operation, so sorting makes the difference.</p> <p>In both queries you return all rows of <code>product_product</code>, but in the first case you sort by a column of <code>django_site</code>, so <code>django_site.domain</code> has to be retrieved in addition, that costs extra. But would not explain the big difference.</p> <p>There is a good chance that the <strong>physical order</strong> of the rows in <code>product_product</code> is already according to the column <code>ordering</code>, which makes the sort in case 2 very cheap and the sort in case 1 expensive.</p> <hr> <p>After "more details added":<br> It is also <strong>considerably more expensive</strong> so sort by <code>character varying(100)</code> than to sort by an <code>integer</code> column. In addition to integer being much smaller, there is also the collation support that slows you down. To verify, try ordering with <code>COLLATE "C"</code>. Read more about <a href="http://www.postgresql.org/docs/current/interactive/collation.html" rel="nofollow noreferrer">collation support in the manual</a>. If you <strong>were</strong> running PostgreSQL 9.1. I see now, that you have PostgreSQL 8.4.</p> <p>Obviously, all rows in the query output have the same value for <code>django_site.domain</code> as you filter on <code>p.site_id = 1</code>. If the query planner was smarter, it might skip the first column for ordering to begin with.</p> <p>You run PostgreSQL 8.4. The query planner of 9.1 has become considerably more intelligent. Upgrading might change the situation, but I can't say for certain.</p> <hr> <p>To verify my theory about physical ordering, you could try and make a copy of your big table with the rows inserted in random order and then run the queries again. Like this:</p> <pre><code>CREATE TABLE p AS SELECT * FROM public.product_product ORDER BY random(); </code></pre> <p>And then:</p> <pre><code>EXPLAIN ANALYZE SELECT p.* FROM p JOIN django_site d ON (p.site_id = d.id) WHERE p.active AND p.site_id = 1 ORDER BY d.domain, p.ordering, p.name; </code></pre> <p>Any difference? --> Obviously that doesn't explain it ...</p> <hr> <p>OK, to test whether the <code>varchar(100)</code> makes the difference, I recreated your scenario. See the <a href="https://stackoverflow.com/a/9908333/939860"><strong>separate answer with a detailed test case and benchmark</strong></a>. This answer is overloaded already.</p> <p>To sum it up:<br> Turns out, my other explanation fits. The main reason for the slowdown is obviously sorting by a <code>varchar(100)</code> column according to a <a href="http://www.postgresql.org/docs/current/interactive/locale.html" rel="nofollow noreferrer">locale (<code>LC_COLLATE</code>)</a>.</p> <p>I added some explanation and links to the <a href="https://stackoverflow.com/a/9908333/939860">test case</a>. The results should speak for themselves.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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