ailabs-software
ailabs-software
/ gist:055a52987d06a38603ebd891a44c52f0
Created
September 15, 2019 03:08
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # EXPLAIN ANALYZE | |
| SELECT maturation_timestamp FROM publish_queue LEFT OUTER JOIN publish ON(publish.id=publish_queue.publish_id) | |
| WHERE status_executed=0 AND funded AND syndicated=false ORDER BY NOT maturation_timestamp IS NULL, maturation_timestamp ASC LIMIT 1; | |
| QUERY PLAN | |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| Limit (cost=193660.99..193660.99 rows=1 width=8) (actual time=668.434..668.434 rows=0 loops=1) | |
| -> Sort (cost=193660.99..194971.58 rows=524239 width=8) (actual time=668.433..668.433 rows=0 loops=1) | |
| Sort Key: ((publish_queue.maturation_timestamp IS NOT NULL)), publish_queue.maturation_timesta |
ailabs-software
/ gist:6739058b82649a7ac3373a8fa256407c
Created
September 15, 2019 03:03
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # EXPLAIN ANALYZE SELECT COUNT(*) FROM publish_queue WHERE NOT EXISTS(SELECT 1 FROM publish WHERE id = publish_queue.publish_id); | |
| QUERY PLAN | |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| Aggregate (cost=444595.43..444595.44 rows=1 width=0) (actual time=4001.970..4001.970 rows=1 loops=1) | |
| -> Merge Anti Join (cost=368.56..444595.43 rows=1 width=0) (actual time=4001.967..4001.967 rows=0 loops=1) | |
| Merge Cond: (publish_queue.publish_id = publish.id) | |
| -> Index Only Scan using publish_queue_publish_id_idx on publish_queue (cost=0.56..226422.09 rows=9668693 width=16) (actual time=0.021..2199.928 rows=9726503 loops=1) | |
| Heap Fetches: 3223208 | |
| -> Materialize (cost=0.43..52748.0 |
ailabs-software
/ gist:04288075c8c1212c79d45d74e850f019
Created
September 15, 2019 02:32
If there are 95040 rows matching status_executed=0, why isn't the number of publish_queue rows being joined on, not 562209?
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # SELECT COUNT(*) FROM publish_queue WHERE status_executed=0; | |
| count | |
| ------- | |
| 95040 | |
| (1 row) | |
| madonna=# EXPLAIN ANALYZE | |
| madonna-# SELECT maturation_timestamp FROM publish_queue LEFT OUTER JOIN publish ON(publish.id=publish_queue.publish_id) WHERE status_executed=0 AND funded AND syndicated=false ORDER BY NOT maturation_timestamp IS NULL, maturation_timestamp ASC LIMIT 1; | |
| QUERY PLAN | |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
ailabs-software
/ gist:db6cfb58a5e7629c036687e141572a80
Created
September 14, 2019 21:19
Shouldn't it be now only joining 95,990 rows to the pubish rows?
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| EXPLAIN ANALYZE | |
| SELECT maturation_timestamp FROM publish_queue LEFT OUTER JOIN publish ON(publish.id=publish_queue.publish_id) WHERE status_executed=0 AND funded AND syndicated=false ORDER BY NOT maturation_timestamp IS NULL, maturation_timestamp ASC LIMIT 1; | |
| QUERY PLAN | |
| ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| Limit (cost=193275.62..193275.63 rows=1 width=8) (actual time=4720.099..4720.100 rows=1 loops=1) | |
| -> Sort (cost=193275.62..194582.79 rows=522866 width=8) (actual time=4720.098..4720.098 rows=1 loops=1) | |
| Sort Key: ((publish_queue.maturation_timestamp IS NOT NULL)), publish_queue.maturatio |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # \d publish | |
| Table "public.publish" | |
| Column | Type | Modifiers | |
| ------------------------+--------------------------+----------- | |
| domain | integer | | |
| user_id | integer | | |
| id | uuid | | |
| issued | timestamp with time zone | | |
| content_id | uuid | | |
| overriding_contacttype | text | |
ailabs-software
/ gist:41405ebee656da7db5dc829c9b6051a8
Created
September 14, 2019 19:36
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| madonna=# EXPLAIN ANALYZE | |
| madonna-# SELECT socialite_message.*, person_id FROM socialite_message LEFT OUTER JOIN socialite_conversation ON(id=convo_id) WHERE socialite_message.domain=131 AND active AND socialite_message.serial>0 ORDER BY serial ASC; | |
| QUERY PLAN | |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | |
| Sort (cost=19705.89..19707.05 rows=464 width=199) (actual time=27.352..27.353 rows=6 loops=1) | |
| Sort Key: socialite_message.serial | |
| Sort Method: quicksort Memory: 26kB | |
| -> Nested Loop (cost=0.85..19685.34 rows=464 width=199) (actual time=0.436..27.332 rows=6 loops=1) | |
| -> Index Scan using socialite_conversation_active_idx on socialite_conversation (cost=0.42..172.18 rows=3271 width=32) (actual time= |
ailabs-software
/ gist:ff0b2845925fbdd5af6c253209ad5a38
Created
September 14, 2019 19:32
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # SET work_mem = '400MB'; | |
| SET | |
| # SHOW work_mem; | |
| work_mem | |
| ---------- | |
| 400MB | |
| (1 row) | |
| # EXPLAIN ANALYZE | |
| SELECT maturation_timestamp FROM publish_queue LEFT OUTER JOIN publish ON(publish.id=publish_queue.publish_id) WHERE status_executed=0 AND funded AND syndicated=false ORDER BY NOT maturation_timestamp IS NULL, maturation_timestamp ASC LIMIT 1; |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # EXPLAIN ANALYZE | |
| SELECT maturation_timestamp FROM publish_queue LEFT OUTER JOIN publish ON(publish.id=publish_queue.publish_id) WHERE status_executed=0 AND funded AND syndicated=false ORDER BY NOT maturation_timestamp IS NULL, maturation_timestamp ASC LIMIT 1; | |
| QUERY PLAN | |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | |
| Limit (cost=193082.23..193082.23 rows=1 width=8) (actual time=1616.985..1616.985 rows=0 loops=1) | |
| -> Sort (cost=193082.23..194389.39 rows=522866 width=8) (actual time=1616.984..1616.984 rows=0 loops=1) | |
| Sort Key: ((publish_queue.maturation_timestamp IS NOT NULL)), publish_queue.matur |
ailabs-software
/ gist:a137d3f44121ce1dbfeaa544a4c713b2
Created
September 14, 2019 19:07
Top 85 queries by total_time
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT total_time, calls, (total_time/calls), query FROM pg_stat_statements ORDER BY total_time DESC; | |
| total_time | calls | ?column? | query |
ailabs-software
/ gist:a2a2d69c676d0d70108845713654e13a
Last active
September 14, 2019 18:54
Postgres Configuration & Out of clients error
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| Problem encountered: | |
| Database a bit slow handling queries when load gets heavy. | |
| Also, eventually ran into this error: "sorry, too many clients already" | |
| What can be done to improve this configuration based on the server? | |
| Postgres config starts on line 138 | |
| SYSTEM MEMORY, VMWARE GUEST (Ubuntu Server): | |
| cat /proc/meminfo: |