Indexing: indexing helps for tuning sql performance

Tables a and b are identical and contain 1000 rows, and a column of type BOX.

select * from a cross join b where (a.b && b.b)     --- 0.28 s

Here 1000000 box overlap (operator &&) tests are completed in 0.28s. The test data set is generated so that the result set contains only 1000 rows.

create index a_b on a using gist(b);
create index b_b on a using gist(b);
select * from a cross join b where (a.b && b.b)     --- 0.01 s

Here the index is used to optimize the cross join, and speed is ridiculous.

Original link: http://stackoverflow.com/questions/5873504/poor-db-performance-when-using-order-by?lq=1