From: Russell Smith <mr-russ@pws.com.au>
To: Pepe Barbe <elventear@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Join performance
Date: Sat, 10 Nov 2007 16:26:17 +1100
Message-ID: <473540F9.7020307@pws.com.au> (raw)
In-Reply-To: <2ACEBABD-7853-475B-B8C0-91B654AF143D@gmail.com>
References: <2ACEBABD-7853-475B-B8C0-91B654AF143D@gmail.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mr-russ@pws.com.au, elventear@gmail.com
Subject: Re: Join performance
In-Reply-To: <473540F9.7020307@pws.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox