From: | Joseph Pravato <joseph(dot)pravato(at)nomagic(dot)com> |
---|---|
To: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
Cc: | "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: PostgreSQL Query Speed Issues |
Date: | 2013-02-27 16:42:09 |
Message-ID: | 512E3761.7070606@nomagic.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On 2/23/2013 10:53 AM, Kevin Grittner wrote:
> That plan choice strikes me as very odd, and not likely to be
> optimal. The only other things that I can think of which might
> cause this plan choice would be if seq_page_cost is higher than
> random_page_cost, or if the table has a lot of dead space in it.
> Could you show EXPLAIN ANALYZE output for the current settings,
> along with the output of running this?:
Sorry for the delay in responding, we thank you for all your assistance
and time, it is very appreciated!
Here is the explain analyze for the query:
select * from contact where id not in (select contact_id from
contact_address_map)
Seq Scan on contact (cost=18995.86..39058.98 rows=287471 width=948)
(actual time=1231.398..1259.205 rows=17 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 574928
SubPlan 1
-> Seq Scan on contact_address_map (cost=0.00..17558.55
rows=574925 width=8) (actual time=0.018..454.653 rows=574928 loops=1)
Total runtime: 1259.281 ms
After your suggestions this query sped up dramatically, it now returns
in less than a second.
This query that we have been talking about is just a sample that we used
to get a start on performance improvements. The original performance
related issue we had was with a large view that we use for our customer
& sales information that accesses 3 additional views and joins a total
of 23 tables. Before the suggestions you gave it returned in 7 - 10
minutes and now returns in less than 10 seconds. However, we have a copy
of our data on another database that runs in less than 0.5 seconds. We
think based on the previous messages in this thread that it is still
choosing a sub-optimal query plan for the views.
This is the explain analyze for our customer data view.
http://pastebin.com/kSfb2dqy
> SELECT name, current_setting(name), source
> FROM pg_settings
> WHERE source NOT IN ('default', 'override');
> SELECT oid, relname, relpages, reltuples FROM pg_class
> WHERE relname = 'contact';
> SELECT * FROM pg_stat_user_tables WHERE relname = 'contact';
Here is the output for the queries you provided.
http://pastebin.com/Yp80HCpe
> --
> Kevin Grittner
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Grittner | 2013-02-27 17:44:26 | Re: PostgreSQL Query Speed Issues |
Previous Message | Thomas Kellerer | 2013-02-26 18:01:22 | Re: PostgreSQL Query Speed Issues |