dekeract

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.

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.