Skip to main content

Posts

Showing posts from January, 2012

Query Tuning Tips

Query Tuning ------------------- CO-Relations (vs) Joins (vs) Sets ++++++++++++++++++++++++++++++++++ Co-Relations/Joins/Sets are actually using for joint or disjoint collections. 1. Co-Relations sub queries are high cost expensive queries when compare to Joins. 2. However, Co-Relations are using less memory when compared to Joins. 3. Joins are always using some builting algorithms like (Hash join (Small set of rows), Merge Join (Huge set of row + Sort)) which makes the things very faster. Find the below examples -> CREATE TABLE TEST(T INT); -> CREATE TABLE TEST1(T1 INT); -> INSERT INTO TEST VALUES(GENERATE_SERIES(1,1000)); -> INSERT INTO TEST VALUES(GENERATE_SERIES(500,1499)); CO-Relation ----------- postgres=# EXPLAIN SELECT * FROM TEST AS OUT WHERE OUT.T IN (SELECT INN.T1 FROM TEST1 AS INN WHERE INN.T1=OUT.T); QUERY PLAN ------------------------------------------------------------------ Seq Scan on test "out" (c