ailabs-software
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 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 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 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 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 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 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 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 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: |
ailabs-software
/ gist:cb9bd2a681a8f519dc67c706db442c9b
Created
July 27, 2019 05:07
This file contains 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=# \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:c4973efe85b7ba4e3495926c40b27c64
Created
July 27, 2019 04:54
This file contains 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, BUFFERS) | |
| SELECT COUNT(*) FROM ( | |
| SELECT publish_id, maturation_timestamp FROM publish_queue | |
| WHERE status_executed=0 | |
| ) ab | |
| INNER JOIN publish ON(publish.funded AND publish.id=ab.publish_id); | |
| QUERY PLAN | |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | |
| Aggregate (cost=142876.33..142876.34 rows=1 width=0) (actual time=1087.664..1087.664 rows=1 loops=1) | |
| Buffers: shared hit=34882, temp read=4781 written=4775 |