Re: Join Bad Performance on different data types

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(dot)adarsh(at)gmail(dot)com>:

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