Re: PostgreSQL Query Speed Issues

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