Hello,
I hope you can provide some answers to a strange problem. This is in
production and is a Severity #1 issue we are having, so any help you can
provide would be appreciated.
PG: PostgreSQL 8.3.7
OS: RHEL 5 64 bit
We have two databases with the same DB schema managing different sets of
On Fri, May 14, 2010 at 1:28 PM, keaton_ad...@mcafee.com wrote:
I did run an ANALYZE, then vacuum full, then another ANALYZE on all tables
involved in the query. That didn't change things. I compared QUERY TUNING
settings in both postgresql.conf files and they are identical. There is a
On Fri, May 14, 2010 at 2:16 PM, Vick Khera vi...@khera.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@postgresql.org)
To make changes to your
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
* keaton_ad...@mcafee.com (keaton_ad...@mcafee.com) wrote:
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.
Are you sure the database schemas are identical, including indexes, etc?
There's an index being used on the
Yes, I triple checked and the schemas, indexes, FKs, triggers all match.
-K
On 5/14/10 12:29 PM, Stephen Frost sfr...@snowman.net wrote:
* keaton_ad...@mcafee.com (keaton_ad...@mcafee.com) wrote:
No luck. I set it in the postgresql.conf file and did a reload, ran analyze
on the tables and
* keaton_ad...@mcafee.com (keaton_ad...@mcafee.com) wrote:
Yes, I triple checked and the schemas, indexes, FKs, triggers all match.
Have you checked over for any enable_* settings that are off? Identical
work_mem and maintenance_work_mem settings?
Thanks,
On Fri, May 14, 2010 at 1:28 PM, keaton_ad...@mcafee.com wrote:
I did run an ANALYZE, then vacuum full, then another ANALYZE on all tables
involved in the query. That didn't change things. I compared QUERY TUNING
settings in both postgresql.conf files and they are identical. There is a
Yes, PG settings are the same. Just checked again.
-K
On 5/14/10 12:54 PM, Stephen Frost sfr...@snowman.net wrote:
* keaton_ad...@mcafee.com (keaton_ad...@mcafee.com) wrote:
Yes, I triple checked and the schemas, indexes, FKs, triggers all match.
Have you checked over for any enable_*
OK, getting closer.
If I comment out the last line ( AND users.user_id NOT IN (SELECT user_id FROM
mxl_user_group)) the optimizer goes for a Merge Join (yea!) and the query runs
in 30 seconds. So something with this NOT IN clause is throwing everything off.
EXPLAIN SELECT
OK,
So if I change the NOT IN clause the query will run with a MERGE JOIN and
complete in about 20 seconds. Have a look at the logic I am following and see
if it makes sense. Might this just be a case where because there is more data
in one DB compared to another (even though the counts are
It looks like it is just a difference in data volume. We are re-working the
query to see what that will do.
Thanks for the suggestions.
-K
On 5/14/10 2:23 PM, Adams, Keaton keaton_ad...@mcafee.com wrote:
OK,
So if I change the NOT IN clause the query will run with a MERGE JOIN and
* keaton_ad...@mcafee.com (keaton_ad...@mcafee.com) wrote:
It looks like it is just a difference in data volume. We are re-working the
query to see what that will do.
Just my 2c, but I'd recommend using JOIN syntax instead of comma-joins.
eg:
select * from a JOIN b USING (col1,col2);
or:
13 matches
Mail list logo
