Re: Join performance

Pepe Barbe wrote:
> Hello,
>
> I am having an issue on PostgreSQL 8.0.12. In the past we had
> performance issues with the query planner for queries on some tables
> where we knew we had indexes and it was doing a sequential scan, and for
> this reason we issue "SET enable_seqscan = FALSE" for some queries.
>
> Recently we have stumbled upon one of these kind of queries that is
> giving terrible performance, because seqscan is disabled. I've reduced
> the problem to a a command like this one:
>
> SELECT * from gsm_sector_metrics NATURAL JOIN gsm_amr_metrics INNER JOIN
> temp_busy_hr USING(start_time,bsc_id,sect_id);
>
> Where temp_busy_hr is a temporary table.

Have you tried analyzing the temp_busy_hr table?
Possibly adding an index to the temp table can help if you are doing lots of queries.

>
> If the previous is issued with seqscan TRUE, it runs within reasonable
> time, else it runs for ever. The query plan for the previous query with
> enable_seqscan = TRUE:

It would be worth know how far the estimates are out.  Also, have you tried altering the statistics target
for relevant columns to increase the accuracy?

>
> QUERY PLAN
>
----------------------------------------------------------------------------------------------------------------------------------------

>
> Limit  (cost=0.00..384555.98 rows=1 width=3092)
>   ->  Nested Loop  (cost=0.00..384555.98 rows=1 width=3092)
>         Join Filter: (("inner".bsc_id = "outer".bsc_id) AND ("inner".site_id = "outer".site_id) AND ("inner".sect_id
="outer".sect_id)) 
>         ->  Nested Loop  (cost=0.00..368645.64 rows=28 width=1192)
>               Join Filter: (("outer".sect_id = "inner".sect_id) AND ("outer".bsc_id = "inner".bsc_id))
>               ->  Seq Scan on temp_busy_hr  (cost=0.00..24.00 rows=1400 width=24)
>               ->  Index Scan using gsm_amr_start_time_idx on gsm_amr_metrics  (cost=0.00..226.66 rows=2094
width=1168)
>                     Index Cond: ("outer".start_time = gsm_amr_metrics.start_time)
>         ->  Index Scan using gsm_sector_start_time_idx on gsm_sector_metrics t1  (cost=0.00..528.77 rows=1973
width=1936)
>               Index Cond: (t1.start_time = "outer".start_time)
> (10 rows)
>
> and the plan for enable_seqscan = FALSE:
>
> QUERY PLAN
>
----------------------------------------------------------------------------------------------------------------------------------------

>
> Limit  (cost=100000097.16.. 100720844.011111 rows=1 width=3092)
>   ->  Nested Loop  (cost=100000097.16..100720844.01 rows=1 width=3092)
>         Join Filter: (("inner".bsc_id = "outer".bsc_id) AND ("inner".site_id = "outer".site_id) AND ("inner".sect_id
="outer".sect_id)) 
>         ->  Merge Join  (cost=100000097.16..100704933.67 rows=28 width=1192)
>               Merge Cond: ("outer".start_time = "inner".start_time)
>               Join Filter: (("inner".sect_id = "outer".sect_id) AND ("inner".bsc_id = "outer".bsc_id))
>               ->  Index Scan using gsm_amr_start_time_idx on gsm_amr_metrics  (cost=0.00..631211.45 rows=6005551
width=1168)
>               ->  Sort  (cost=100000097.16..100000100.66 rows=1400 width=24)
>                     Sort Key: temp_busy_hr.start_time
>                     ->  Seq Scan on temp_busy_hr  (cost=100000000.00..100000024.00 rows=1400 width=24)
>         ->  Index Scan using gsm_sector_start_time_idx on gsm_sector_metrics t1  (cost=0.00..528.77 rows=1973
width=1936)
>               Index Cond: (t1.start_time = "outer".start_time)
> (12 rows)
>
> Any ideas what could I try to fix this problem?
>
> Thanks,
> Pepe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>