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 = 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 = 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.
