Re: Poor query performance on one of two "like" databases in production.

No luck. I set it in the postgresql.conf file and did a reload, ran analyze on the tables and the query plan isn't any better.

mxl=# show default_statistics_target;
default_statistics_target
---------------------------
100
(1 row)

mxl=# analyze mxl_domain;
ANALYZE
mxl=# analyze mxl_domain_alias;
ANALYZE
mxl=# analyze mxl_user;
ANALYZE
mxl=# analyze mxl_user_alias;
ANALYZE
mxl=# analyze mxl_user_group;
ANALYZE
mxl=# analyze wds_policy_set;
ANALYZE
mxl=# \q
postgres(at)p01c06d130>
postgres(at)p01c06d130>
postgres(at)p01c06d130>
postgres(at)p01c06d130> set -o vi
postgres(at)p01c06d130>
postgres(at)p01c06d130> psql -Upostgres -dmxl -fbadquery.sql
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=14312.76..2310264747.40 rows=244768 width=1564)
-> Hash Join (cost=14312.76..2309439666.13 rows=1994364 width=1564)
Hash Cond: ((u.customer_id = d.customer_id) AND (u.domain_id = d.domain_id))
-> Append (cost=2935.45..2289184035.49 rows=1028871 width=1044)
-> Index Scan using mxl_user_domain_id_idx on mxl_user u (cost=2935.45..2017782497.30 rows=902944 width=52)
Filter: ((active <> 0) AND (NOT (subplan)))
SubPlan
-> Materialize (cost=2935.45..4761.59 rows=131314 width=4)
-> Seq Scan on mxl_user_group (cost=0.00..2291.14 rows=131314 width=4)
-> Subquery Scan "*SELECT* 2" (cost=2943.64..271401538.19 rows=125927 width=1044)
Filter: (NOT (subplan))
-> Merge Join (cost=8.19..95177.59 rows=251854 width=55)
Merge Cond: (u.user_id = ua.user_id)
-> Index Scan using mxl_user_pkey on mxl_user u (cost=0.00..77340.62 rows=1873068 width=28)
-> Index Scan using mxl_user_alias_uid_idx on mxl_user_alias ua (cost=0.00..10109.21 rows=251854 width=31)
Filter: (ua.active <> 0)
SubPlan
-> Materialize (cost=2935.45..4761.59 rows=131314 width=4)
-> Seq Scan on mxl_user_group (cost=0.00..2291.14 rows=131314 width=4)
-> Hash (cost=4989.27..4989.27 rows=77536 width=528)
-> Append (cost=0.00..4989.27 rows=77536 width=528)
-> Seq Scan on mxl_domain d (cost=0.00..1810.81 rows=64385 width=28)
Filter: (active = 1)
-> Subquery Scan "*SELECT* 2" (cost=2454.66..3178.46 rows=13151 width=528)
-> Hash Join (cost=2454.66..3046.95 rows=13151 width=33)
Hash Cond: (da.domain_id = d.domain_id)
-> Seq Scan on mxl_domain_alias da (cost=0.00..296.39 rows=13151 width=25)
Filter: (active = 1)
-> Hash (cost=1649.85..1649.85 rows=64385 width=12)
-> Seq Scan on mxl_domain d (cost=0.00..1649.85 rows=64385 width=12)
-> Index Scan using wds_policy_set_id_idx on wds_policy_set p (cost=0.00..0.39 rows=2 width=8)
Index Cond: (p.id = u.customer_id)
Filter: ((p.default_flag = 1) AND (p.web_access_flag = 1) AND (p.active = 1) AND (p.scope = 3))
(33 rows)

I also tried a REINDEX / ANALYZE on all of the involved tables and that didn't help either:

mxl=# reindex table mxl_user;
REINDEX
mxl=# reindex table mxl_user_alias;
REINDEX
mxl=# reindex table mxl_domain;
REINDEX
mxl=# reindex table mxl_domain_alias;
REINDEX
mxl=# reindex table mxl_user_group;
REINDEX
mxl=# reindex table wds_policy_set;
REINDEX

mxl=# analyze mxl_user;
ANALYZE
mxl=# analyze mxl_user_alias;
ANALYZE
mxl=# analyze mxl_domain;
ANALYZE
mxl=# analyze mxl_domain_alias;
ANALYZE
mxl=# analyze mxl_user_group;
ANALYZE
mxl=# analyze wds_policy_set;
ANALYZE

On 5/14/10 12:16 PM, "Vick Khera" <vivek(at)khera(dot)org> wrote:

On Fri, May 14, 2010 at 2:16 PM, Vick Khera <vivek(at)khera(dot)org> wrote:
> What's your default_statistics_target value? ie, run "select
> default_statistics_target;"
>

sorry... "show default_statistics_target;"

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general