From: | Adarsh Sharma <eddy(dot)adarsh(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Join Bad Performance on different data types |
Date: | 2014-03-04 06:57:50 |
Message-ID: | CAGx-Qq+nPn_YEPjfuH-kkf2_DURx38Urfxvwjz4nze2Z8yCiBQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
Today i need to change datatype of one of my tables from *bigint to
bigint[] *due to
application requirements. But One of my query hangs after this change :-
select DISTINCT glt.id || ':' || gtt.name as id_type, glt.name, latitude,
longitude, radius, latitude || ',' || longitude as latlon from graph1 glt,
graph2 gcr, graph3 gtd, graph5 td, graph6 gtt where gtt.id=td.entity_type_id
AND glt.id=ANY(gtd.id_list) and *gtd.id <http://gtd.id> = gcr.t_ids*) AND
gcr.id=ANY(td.detail_id);
*Explain Analyze Expected time : Total runtime: 19460.809 ms*
select DISTINCT glt.id || ':' || gtt.name as id_type, glt.name, latitude,
longitude, radius, latitude || ',' || longitude as latlon from graph1 glt,
graph2 gcr, graph3 gtd, graph5 td, graph6 gtt where gtt.id=td.entity_type_id
AND glt.id=ANY(gtd.id_list) and *gtd.id <http://gtd.id> = ANY(gcr.t_ids)*AND
gcr.id=ANY(td.detail_id);
Query not finishing from last 1 hour. Tables size are ~ 5k rows and fairly
small tables.
I tried creating simple and gin indexes on the column(t_ids) but still not
helping. Anyone has any idea or faced this before. Postgresql version is
9.2.
Thanks
From | Date | Subject | |
---|---|---|---|
Next Message | Sameer Kumar | 2014-03-04 07:43:42 | Re: Join Bad Performance on different data types |
Previous Message | Tatsuo Ishii | 2014-03-04 06:27:26 | Re: How to recovery failed master after failover |