Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <h2>Test Case</h2> <p>PostgreSQL 9.1. Test database with limited resources, but way enough for this small case. The locale for collation will be relevant:</p> <pre><code>SHOW LC_COLLATE; de_AT.UTF-8 </code></pre> <p>Step 1) Reconstruct raw test environment</p> <pre><code>-- DROP TABLE x; CREATE SCHEMA x; -- test schema -- DROP TABLE x.django_site; CREATE TABLE x.django_site ( id serial primary key ,domain character varying(100) not null ,int_col int not null ); INSERT INTO x.django_site values (1,'www.testsite.com/foodir/', 3); -- DROP TABLE x.product; CREATE TABLE x.product ( id serial primary key ,site_id integer not null ,name character varying(255) not null ,slug character varying(255) not null ,sku character varying(255) ,ordering integer not null ,active boolean not null ); INSERT INTO x.product (site_id, name, slug, sku, ordering, active) SELECT 1 ,repeat(chr((random() * 255)::int + 32), (random()*255)::int) ,repeat(chr((random() * 255)::int + 32), (random()*255)::int) ,repeat(chr((random() * 255)::int + 32), (random()*255)::int) ,i -- ordering in sequence ,NOT (random()* 0.5174346569119122)::int::bool FROM generate_series(1, 17540) AS x(i); -- SELECT ((591::float8 / 17540)* 0.5) / (1 - (591::float8 / 17540)) -- = 0.5174346569119122 CREATE INDEX product_site_id on x.product(site_id); </code></pre> <p>Step 2) ANALYZE</p> <pre><code> ANALYZE x.product; ANALYZE x.django_site; </code></pre> <p>Step 3) Reorder BY random()</p> <pre><code>-- DROP TABLE x.p; CREATE TABLE x.p AS SELECT * FROM x.product ORDER BY random(); ANALYZE x.p; </code></pre> <hr> <h3>Results</h3> <pre><code>EXPLAIN ANALYZE SELECT p.* FROM x.p JOIN x.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 -- ORDER BY p.ordering, p.name -- ORDER BY d.id, p.ordering, p.name -- ORDER BY d.int_col, p.ordering, p.name -- ORDER BY p.name COLLATE "C" -- ORDER BY d.domain COLLATE "C", p.ordering, p.name -- dvd's final solution </code></pre> <p>1) Pre ANALYZE (-> bitmap index scan)<br> 2) Post ANALYZE (-> seq scan)<br> 3) Re-order by random(), ANALYZE</p> <pre><code>ORDER BY d.domain, p.ordering, p.name </code></pre> <p>1) Total runtime: 1253.543 ms<br> 2) Total runtime: 1250.351 ms<br> 3) Total runtime: 1283.111 ms </p> <pre><code>ORDER BY p.ordering, p.name </code></pre> <p>1) Total runtime: 177.266 ms<br> 2) Total runtime: 174.556 ms<br> 3) Total runtime: 177.797 ms </p> <pre><code>ORDER BY d.id, p.ordering, p.name </code></pre> <p>1) Total runtime: 176.628 ms<br> 2) Total runtime: 176.811 ms<br> 3) Total runtime: 178.150 ms<br> <em>The planner obviously factors in that <code>d.id</code> is functionally dependent.</em></p> <pre>ORDER BY <b>d.int_col</b>, p.ordering, p.name -- integer column in other table</pre> <p>1) Total runtime: 242.218 ms -- !!<br> 2) Total runtime: 245.234 ms<br> 3) Total runtime: <strong>254.581 ms</strong><br> <em>The planner obviously misses that <code>d.int_col</code> (NOT NULL) is just as functionally dependent. But sorting by an integer column is cheap.</em></p> <pre>ORDER BY <b>p.name</b> -- varchar(255) in same table</pre> <p>1) Total runtime: 2259.171 ms -- !!<br> 2) Total runtime: 2257.650 ms<br> 3) Total runtime: <strong>2258.282 ms</strong><br> <em>Sorting by a (long) <code>varchar</code> or <code>text</code> column is expensive ...</em></p> <pre>ORDER BY p.name <b>COLLATE "C"</b></pre> <p>1) Total runtime: 327.516 ms -- !!<br> 2) Total runtime: 325.103 ms<br> 3) Total runtime: <strong>327.206 ms</strong><br> <em>... but not as expensive if done without locale.</em></p> <p>With the locale out of the way, sorting by a <code>varchar</code> column is not quite but almost as fast. Locale <code>"C"</code> is effectively "no locale, just order by byte value". I <a href="http://www.postgresql.org/docs/current/interactive/locale.html#AEN31366" rel="noreferrer">quote the manual</a>:</p> <blockquote> <p>If you want the system to behave as if it had no locale support, use the special locale name C, or equivalently POSIX.</p> </blockquote> <hr> <p>Putting it all together, @dvd chose:</p> <pre><code>ORDER BY d.domain COLLATE "C", p.ordering, p.name </code></pre> <p>... 3) Total runtime: <strong>275.854 ms</strong><br> <em>That should do.</em></p>
    singulars
    1. This table or related slice is empty.
    1. POSlow query ordering by a column in a joined table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USErwin Brandstetter
    1. USErwin Brandstetter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    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
    1. COYou beat me again. I just finished typing the table-creation and population. In fact the optimiser should see that d.domain is functionally dependant on p.site_id = d.id. I even added an FK constraint, but that did not help. Also: the rowsize in the OP is very large, about 1K.
      singulars
      1. PO
      1. USwildplasser
    2. COYeah, the optimizer has a blind spot there. As `p.site_id` has a `=` condition on it and the other involved columns `d.id, d.domain` are NOT NULL, ordering by `d.domain` logically has no effect. There is potential for a smarter planner. Do you think the row size has an additional effect?
      singulars
      1. PO
      1. USErwin Brandstetter
    3. COIt looks like the 25M work mem accomodates all the 17K * 1K records. And the final ORDER BY has to walk the full length if the *identical* .domain strings. The optimiser *could* have known this (Functional dependance site_id -> domain leading to equivalence classes for the domain<->domain compare. Character encoding / collation seems to make things even worse). I'll look into the source, when I find time. Maybe inform Tom Lane?
      singulars
      1. PO
      1. USwildplasser
 

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