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" (cost=0.00..48043.0…