Re: Set of related slow queries

------------------------------+---------------------------------------------
> shared_buffers | 16MB
> work_mem | 250MB

This seems a bit suspicious. Are you sure you want to keep the
shared_buffers so small and work_mem so large at the same time? There
probably are workloads where this is the right thing to do, but I doubt
this is the case. Why have you set it like this?

I don't have much experience with running Pg on AWS, but I'd try to
increase the shared buffers to say 512MB and decrease the work_mem to 16MB
(or something like that).

Undersized shared_buffers might actually be part of the problem - to
access a row, the page needs to be loaded into shared_buffers. Even though
the I/O is very fast (or the page is already in the filesystem page
cache), there's some locking etc. that needs to be done. When the cache is
small (e.g. 16MB) then the pages need to be removed and read again
frequently. This might be one of the reasons why the CPU is 100% utilized.

> SELECT "logparser_entry"."id" ,
> "logparser_entry"."log_id" ,
> "logparser_entry"."encounter_id" ,
> "logparser_entry"."entry_order" ,
> "logparser_entry"."timestamp" ,
> "logparser_entry"."seconds_since_start" ,
> "logparser_entry"."event_type" ,
> "logparser_entry"."actor_id" ,
> "logparser_entry"."actor_relation" ,
> "logparser_entry"."target_id" ,
> "logparser_entry"."target_relation" ,
> "logparser_entry"."pet_owner_id" ,
> "logparser_entry"."pet_owner_relation" ,
> "logparser_entry"."pet_target_owner_id" ,
> "logparser_entry"."pet_target_owner_relation",
> "logparser_entry"."ability_id" ,
> "logparser_entry"."effective_value" ,
> "logparser_entry"."blocked" ,
> "logparser_entry"."absorbed" ,
> "logparser_entry"."overkill" ,
> "logparser_entry"."overheal" ,
> "logparser_entry"."total_value"
> FROM "logparser_entry"
> WHERE (
> "logparser_entry"."log_id" = 2
> AND NOT
> (
> (
> "logparser_entry"."actor_relation"
> IN (E'Other',
>
> E'N/A')
> AND "logparser_entry"."actor_relation"
> IS NOT NULL
> )
> )
> AND "logparser_entry"."event_type" IN (E'Attack' ,
> E'DoT Tick',
> E'Critical Attack')
> )
> ORDER BY "logparser_entry"."entry_order" ASC
> LIMIT 1
> http://explain.depesz.com/s/vEx

Well, the problem with this is that it needs to evaluate the whole result
set, sort it by "entry_order" and then get the 1st row. And there's no
index on entry_order, so it has to evaluate the whole result set and then
perform a traditional sort.

Try to create an index on the "entry_order" column - that might push it
towards index scan (to be honest I don't know if PostgreSQL knows it can
do it this way, so maybe it won't work).

> SELECT (ROUND(logparser_entry.seconds_since_start / 42)) AS "interval",
> SUM("logparser_entry"."effective_value") AS
> "effective_value__sum"
> FROM "logparser_entry"
> WHERE (
> "logparser_entry"."log_id" = 2
> AND NOT
> (
> (
> "logparser_entry"."actor_relation"
> IN (E'Other',
>
> E'N/A')
> AND "logparser_entry"."actor_relation"
> IS NOT NULL
> )
> )
> AND "logparser_entry"."event_type" IN (E'Attack' ,
> E'DoT Tick',
> E'Critical Attack')
> )
> GROUP BY (ROUND(logparser_entry.seconds_since_start / 42)),
> ROUND(logparser_entry.seconds_since_start / 42)
> ORDER BY "interval" ASC
> http://explain.depesz.com/s/Rhb

Hm, this is probably the best plan possible - not sure how to make it
faster. I'd expect a better performance with larger shared_buffers.

> http://explain.depesz.com/s/JUo

Same as above. Good plan, maybe increase shared_buffers?

> http://explain.depesz.com/s/VZA

Same as above. Good plan, maybe increase shared_buffers.

regards
Tomas