------------------------------+--------------------------------------------- > 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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
