Join Bad Performance on different data types

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

Responses

Browse pgsql-general by date

  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