Re: Join Bad Performance on different data types

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hello

PostgreSQL doesn't use index when types on left and right part are not equal.

Probably you lost some index - you can see a difference in EXPLAIN SELECT ...

Regards

Pavel

2014-03-04 7:57 GMT+01:00 Adarsh Sharma <eddy.adarsh@xxxxxxxxx>:

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

Thanks