Test Case
PostgreSQL 9.1. Test database with limited resources, but way enough for this small case. The locale for collation will be relevant:
SHOW LC_COLLATE;
de_AT.UTF-8
Step 1) Reconstruct raw test environment
-- 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);
Step 2) ANALYZE
ANALYZE x.product;
ANALYZE x.django_site;
Step 3) Reorder BY random()
-- DROP TABLE x.p;
CREATE TABLE x.p AS
SELECT *
FROM x.product
ORDER BY random();
ANALYZE x.p;
Results
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
1) Pre ANALYZE (-> bitmap index scan)
2) Post ANALYZE (-> seq scan)
3) Re-order by random(), ANALYZE
ORDER BY d.domain, p.ordering, p.name
1) Total runtime: 1253.543 ms
2) Total runtime: 1250.351 ms
3) Total runtime: 1283.111 ms
ORDER BY p.ordering, p.name
1) Total runtime: 177.266 ms
2) Total runtime: 174.556 ms
3) Total runtime: 177.797 ms
ORDER BY d.id, p.ordering, p.name
1) Total runtime: 176.628 ms
2) Total runtime: 176.811 ms
3) Total runtime: 178.150 ms
The planner obviously factors in that d.id is functionally dependent.
ORDER BY d.int_col, p.ordering, p.name -- integer column in other table
1) Total runtime: 242.218 ms -- !!
2) Total runtime: 245.234 ms
3) Total runtime: 254.581 ms
The planner obviously misses that d.int_col (NOT NULL) is just as functionally dependent. But sorting by an integer column is cheap.
ORDER BY p.name -- varchar(255) in same table
1) Total runtime: 2259.171 ms -- !!
2) Total runtime: 2257.650 ms
3) Total runtime: 2258.282 ms
Sorting by a (long) varchar or text column is expensive ...
ORDER BY p.name COLLATE "C"
1) Total runtime: 327.516 ms -- !!
2) Total runtime: 325.103 ms
3) Total runtime: 327.206 ms
... but not as expensive if done without locale.
With the locale out of the way, sorting by a varchar column is not quite but almost as fast. Locale "C" is effectively "no locale, just order by byte value". I quote the manual:
If you want the system to behave as if it had no locale support, use
the special locale name C, or equivalently POSIX.
Putting it all together, @dvd chose:
ORDER BY d.domain COLLATE "C", p.ordering, p.name
...
3) Total runtime: 275.854 ms
That should do.
