2014-03-05 6:53 GMT+02:00 Adarsh Sharma <eddy.adarsh@xxxxxxxxx>:
Anyhow working on finding the root cause.
I would do the following:
1. Check your `default_statistics_target`, like:
SHOW default_statistics_target;
2. Your previously posted `EXPLAIN ANALYZE` with `set enable_seqscan=off` shows
a significant skew in the estiamted number of rows. I would really bump up the
`default_statistics_target` higher (modify your `postgresql.conf` and reload),
up to 500, re-analyzed all tables and checked the performance again.
If you don't want to do it for the whole cluster, then increase for individual columns.
I think increasing it up to 500 (or higher) for the columns containing arrays will be
beneficial in any case:
ALTER TABLE graph5 ALTER detail_id SET STATISTICS 1000;
ALTER TABLE graph3 ALTER id_list SET STATISTICS 1000;
ALTER TABLE graph2 ALTER targeting_ids SET STATISTICS 1000;
Of course, you need to `ANALYZE` (or `VACUUM ANALYZE`) the tables after this change.
Of course, you need to `ANALYZE` (or `VACUUM ANALYZE`) the tables after this change.
3. Next, I've reformatted your query the following way (easier for me):
--
Victor Y. Yegorov
SELECT DISTINCT
glt.name,latitude,longitude,radius,
latitude || ',' || longitude as latlon
FROM graph5 td
JOIN graph6 gtt ON gtt.id=td.entity_type_id
JOIN graph2 gcr ON gcr.id=ANY(td.detail_id)
JOIN graph3 gtd ON gtd.id=gcr.t_ids
JOIN graph1 glt ON glt.id=ANY(gtd.id_list);
I've ordered joins (well, tables) in the way they depend on each other.
I would try out to move the first join into the CTE in order to isolate it's results
and help optimizer to better estiamte the rows. Something like this:
WITH first AS (
SELECT td.*,gtt.*
FROM graph5 td
JOIN graph6 gtt ON gtt.id=td.entity_type_id
)
SELECT DISTINCT
glt.id || ':' || first.name as id_type,
glt.name,latitude,longitude,radius,
latitude || ',' || longitude as latlon
FROM first
JOIN graph2 gcr ON gcr.id=ANY(first.detail_id)
JOIN graph3 gtd ON gtd.id=gcr.t_ids
JOIN graph1 glt ON glt.id=ANY(gtd.id_list)
4. Try disabling materialization, like `set enable_material=off`.
Victor Y. Yegorov
