On Wed, Mar 23, 2011 at 12:50 PM, Samuel Gendler <sgendler@xxxxxxxxxxxxxxxx> wrote:
On Tue, Mar 22, 2011 at 11:28 PM, Adarsh Sharma <adarsh.sharma@xxxxxxxxxx> wrote:please describe what your query is trying to select, as it is possible that query isn't doing what you think it is. joining 2 tables where id1 != id2 will create a cross multiple of the two tables such that every row from the first table is matched with every single row from the second table that doesn't have a matching id. Then you are looking for distinct values on that potentially enormous set of rows.
I perform a join query on it as :
explain analyze select distinct(p.crawled_page_id) from page_content p , clause2 c where p.crawled_page_id != c.source_id ;
What it takes more than 1 hour to complete. As I issue the explain analyze command and cannot able to wait for output but I send my explain output as :db_v2=# select * from table1;id | value----+-------1 | 12 | 23 | 3(3 rows)db_v2=# select * from table2;id | value----+-------1 | 42 | 53 | 6(3 rows)id | value | id | value----+-------+----+-------1 | 1 | 2 | 51 | 1 | 3 | 62 | 2 | 1 | 42 | 2 | 3 | 63 | 3 | 1 | 43 | 3 | 2 | 5So if you have a couple of million rows in each table, you are selecting distinct over a potentially huge set of data. If you are actually trying to find all ids from one table which have no match at all in the other table, then you need an entirely different query:
Check this setup:
pg=# create table t1(a int, b int);
CREATE TABLE
pg=# create index t1_b on t1(b);
CREATE INDEX
pg=# create table t2(c int, d int);
CREATE TABLE
pg=# create index t2_cd on t2(c,d);
CREATE INDEX
pg=# explain select distinct(b) from t1,t2 where t1.b !=t2.d;
QUERY PLAN
-------------------------------------------------------------------------------
Unique (cost=0.00..80198.86 rows=200 width=4)
-> Nested Loop (cost=0.00..68807.10 rows=4556702 width=4)
Join Filter: (t1.b <> t2.d)
-> Index Scan using t1_b on t1 (cost=0.00..76.35 rows=2140 width=4)
-> Materialize (cost=0.00..42.10 rows=2140 width=4)
-> Seq Scan on t2 (cost=0.00..31.40 rows=2140 width=4)
(6 rows)
pg=# explain select distinct(b) from t1 where NOT EXISTS (select 1 from t2 where t2.d=t1.b);
QUERY PLAN
------------------------------------------------------------------------
HashAggregate (cost=193.88..193.89 rows=1 width=4)
-> Hash Anti Join (cost=58.15..193.88 rows=1 width=4)
Hash Cond: (t1.b = t2.d)
-> Seq Scan on t1 (cost=0.00..31.40 rows=2140 width=4)
-> Hash (cost=31.40..31.40 rows=2140 width=4)
-> Seq Scan on t2 (cost=0.00..31.40 rows=2140 width=4)
(6 rows)
The cost seems to be on higher side, but maybe on your system with index scan on t2 and t1, the cost might be on lower side.
Another query which forced index scan was :
pg=# explain select distinct(b) from t1,t2 where t1.b >t2.d union all select distinct(b) from t1,t2 where t1.b <t2.d;
QUERY PLAN
-------------------------------------------------------------------------------------
Append (cost=0.00..100496.74 rows=400 width=4)
-> Unique (cost=0.00..50246.37 rows=200 width=4)
-> Nested Loop (cost=0.00..46430.04 rows=1526533 width=4)
-> Index Scan using t1_b on t1 (cost=0.00..76.35 rows=2140 width=4)
-> Index Scan using t2_d on t2 (cost=0.00..12.75 rows=713 width=4)
Index Cond: (public.t1.b > public.t2.d)
-> Unique (cost=0.00..50246.37 rows=200 width=4)
-> Nested Loop (cost=0.00..46430.04 rows=1526533 width=4)
-> Index Scan using t1_b on t1 (cost=0.00..76.35 rows=2140 width=4)
-> Index Scan using t2_d on t2 (cost=0.00..12.75 rows=713 width=4)
Index Cond: (public.t1.b < public.t2.d)
(11 rows)
This looks like to a acceptable.
Please try this above query with your setup and post the explain output.
--
Regards,
Chetan Suttraway
EnterpriseDB, The Enterprise PostgreSQL company.
