News for hackers. Stuff that matters.
Re:Ask HN: What problem in your industry is a potential star... by knodi123 on Monday June 29, 2015 @02:58PM (#9800150)
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, 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).

Re:Ask HN: What problem in your industry is a potential star... by dekeract on Monday June 29, 2015 @07:49PM (#9801961)
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/ to see it in a prettier format, especially with analyze.

Set up pgBadger https://github.com/dalibo/pgbadger and see https://wiki.postgresql.org/wiki/Index_Maintenance for more information about your indexes.

Re:Ask HN: What problem in your industry is a potential star... by knodi123 on Monday June 29, 2015 @08:52PM (#9802156)
> Put it into http://explain.depesz.com/ to see it in a prettier format

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?

Re:Ask HN: What problem in your industry is a potential star... by dekeract on Tuesday June 30, 2015 @12:30AM (#9802967)
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.

Re:Ask HN: What problem in your industry is a potential star... by thorin on Tuesday June 30, 2015 @10:38AM (#9805526)
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?

Re:Ask HN: What problem in your industry is a potential star... by knodi123 on Tuesday June 30, 2015 @02:54PM (#9807617)
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!