Set up pgBadger https://github.com/dalibo/pgbadger and see https://wiki.postgresql.org/wiki/Index_Maintenance for more information about your indexes.
I tried that.... 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?
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.
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?
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!
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, 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).