4 ms·

We need a DBA in a box. I'm setting up a brand new postgres solution to replace an existing highly-tuned MSSQL solution, and the pain points are all down to di

We need a DBA in a box. I'm setting up a brand new postgres solution to replace an existing highly-tuned MSSQL solution, and the pain points are all down to differing performance profiles. I have an SeoKeywordsProfile query that takes 3.5 seconds in MSSQL, and >12 hrs on postgres. Clearly there's an index that would solve my problem, but the query is 3 pages of SQL with no whitespace! There's https://github.com/cohenjo/pg_idx_advisor https://github.com/cohenjo/pg_idx_advisor, but it only works on one specific release of postgres and not on any other. I'd like something that could just monitor my database for a fixed period of time and tell me what indexes aren't being used, what indexes ought to be used, and make any other suggestions that might help (partition this table, vacuum that table, etc).

dekeract 11y ago
Run an explain on that three page query, with analyze after some changes so you don't have to wait 12 hours. Put it into http://explain.depesz.com/ http://explain.depesz.com/ to see it in a prettier format, especially with analyze. Set up pgBadger https://github.com/dalibo/pgbadger https://github.com/dalibo/pgbadger and see https://wiki.postgresql.org/wiki/Index_Maintenance https://wiki.postgresql.org/wiki/Index_Maintenance for more information about your indexes.
- knodi123 11y ago
  > Put it into http://explain.depesz.com/ http://explain.depesz.com/ to see it in a prettier format I tried that.... http://explain.depesz.com/s/22H5 http://explain.depesz.com/s/22H5 The problem is, I'm unsure how to read this. I can explain, but I can't explain analyze, since it's too slow. Can you make heads or tails of it?
  - dekeract 11y ago
    Since I know neither the schema nor the query you're running, I can only give you so much. You can look at costs to narrow in on a few spots. Lines 30-32 indicate that you are pulling a lot of data from buyclicks and transactions. This makes up a huge portion of the cost of this query. Lines 33-40 are very similar. Looks like you are probably pulling more data than you need, and you'll see a lot of improvement if you can reduce these two spots. Fixing those two parts will make all the nested loops and hash joins that use this data a lot faster. Looks like there are several sequential scans (Seq Scan) that you could likely improve with a quick index on the relevant columns. You seem to be filtering for similar things on ad_codes in a main query and a subquery, which probably could be refactored out. These are small optimizations compared to the first. My email is in my profile if you want to talk about this more.
  - thorin 11y ago
    I guess stuff like this: Hash Join (cost=78,396.37..2,150,742.13 rows=24,442,180 width=40) (actual rows= loops=) Hash Cond: (buyclicks.offer_id = offers.id) if you can filter down number of rows in these cases or maybe only return columns present in the index and/or add an index where required you will speed up. Watch out it doesn't affect insert/update too much though if you add an index. You have created fk indexes right?
    - knodi123 11y ago
      Thanks for the tip. I'm not so worried about insert/update, since this is a reporting database and the only inserts are done by backgrounded processes. Yes, we have fk indexes. I don't see how we can filter down the number of rows, since the purpose of the outer query is to get a count of how many buyclicks there were for each offer. But I'll see if I can create a covering index that reduces the join cost. Thanks again!

dekeract 11y ago

Run an explain on that three page query, with analyze after some changes so you don't have to wait 12 hours. Put it into http://explain.depesz.com/ http://explain.depesz.com/ to see it in a prettier format, especially with analyze. Set up pgBadger https://github.com/dalibo/pgbadger https://github.com/dalibo/pgbadger and see https://wiki.postgresql.org/wiki/Index_Maintenance https://wiki.postgresql.org/wiki/Index_Maintenance for more information about your indexes.

knodi123 11y ago
> Put it into http://explain.depesz.com/ http://explain.depesz.com/ to see it in a prettier format I tried that.... http://explain.depesz.com/s/22H5 http://explain.depesz.com/s/22H5 The problem is, I'm unsure how to read this. I can explain, but I can't explain analyze, since it's too slow. Can you make heads or tails of it?
- dekeract 11y ago
  Since I know neither the schema nor the query you're running, I can only give you so much. You can look at costs to narrow in on a few spots. Lines 30-32 indicate that you are pulling a lot of data from buyclicks and transactions. This makes up a huge portion of the cost of this query. Lines 33-40 are very similar. Looks like you are probably pulling more data than you need, and you'll see a lot of improvement if you can reduce these two spots. Fixing those two parts will make all the nested loops and hash joins that use this data a lot faster. Looks like there are several sequential scans (Seq Scan) that you could likely improve with a quick index on the relevant columns. You seem to be filtering for similar things on ad_codes in a main query and a subquery, which probably could be refactored out. These are small optimizations compared to the first. My email is in my profile if you want to talk about this more.
- thorin 11y ago
  I guess stuff like this: Hash Join (cost=78,396.37..2,150,742.13 rows=24,442,180 width=40) (actual rows= loops=) Hash Cond: (buyclicks.offer_id = offers.id) if you can filter down number of rows in these cases or maybe only return columns present in the index and/or add an index where required you will speed up. Watch out it doesn't affect insert/update too much though if you add an index. You have created fk indexes right?
  - knodi123 11y ago
    Thanks for the tip. I'm not so worried about insert/update, since this is a reporting database and the only inserts are done by backgrounded processes. Yes, we have fk indexes. I don't see how we can filter down the number of rows, since the purpose of the outer query is to get a count of how many buyclicks there were for each offer. But I'll see if I can create a covering index that reduces the join cost. Thanks again!