Database Reference
In-Depth Information
JOIN sfpoly sf
ON ST_Intersects(sf.geom,
ST_Transform(sc.geom, 3310));
2. We get the following performancenumbers for three query runs:
Time: 274.619 ms
Time: 255.102 ms
Time: 295.135 ms
3. Clusterthe
caschools
tableusingthe
caschools_geom_3310_idx
index
as follows:
CLUSTER caschools
USING caschools_geom_3310_idx;
4. Rerunthequeryfromthefirststepthreetimesforthefollowingperformance
timings:
Time: 242.878 ms
Time: 220.739 ms
Time: 238.378 ms
5. The performance improvements were not significant.
How it works...
Usingthe
CLUSTER
statementonthe
caschools
tabledidnotresultinasignificant
performanceboost.Thelessonhereisthatthereisnoguaranteethatqueryperform-
ancewillimproveonaclusteredtable.Clusteringshouldbereservedfortableswith
manylargerecordsandonlyafteraddingtheappropriateindexestoandoptimizing
queries for the tables in question.
