Just delete 50.000.000 of rows on a PostgreSQL table and DB still very slow

sorry for my poor english.

I have a postgres DB runing on amazon RDS (db.t3.small), with django as a backend. i have made a mistake and created 50.000.000 rows. when i figure out (because queries on that table where ultra slow) i delete it all. but the queries i make on that table stills super slow. it only have 300 rows now.

i have to clean some cache? i have to wait something? the configuration of the RDS in aws is default.

the engine version of postgres is 12.5, also have postgis installed in it.

i check for vacuum issues and run this command:

SELECT relname AS TableName,n_live_tup AS LiveTuples,n_dead_tup AS DeadTuples,last_autovacuum AS Autovacuum,last_autoanalyze AS Autoanalyze FROM pg_stat_user_tables; 

the table with the problem says:

'appointment_timeslot', 1417, 0, datetime.datetime(2021, 7, 21, 18, 13, 8, 193967, tzinfo=<UTC>), datetime.datetime(2021, 7, 21, 18, 13, 30, 551750, tzinfo=<UTC>) 

check for indexes that Django creates automaticly on that table and i find 4

[ ('appointment_timeslot_pkey', 'CREATE UNIQUE INDEX appointment_timeslot_pkey ON public.appointment_timeslot USING btree (id)') 'appointment_timeslot_home_visit_id_62df4faf', 'CREATE INDEX appointment_timeslot_home_visit_id_62df4faf ON public.appointment_timeslot USING btree (home_visit_id)') ('appointment_timeslot_office_id_6871b47b', 'CREATE INDEX appointment_timeslot_office_id_6871b47b ON public.appointment_timeslot USING btree (office_id)') ('appointment_timeslot_time_range_id_715578fa', 'CREATE INDEX appointment_timeslot_time_range_id_715578fa ON public.appointment_timeslot USING btree (time_range_id)') ] 

Unexpected very slow user count in WordPress

I have a WordPress page that returns timeout error every so often. When reviewing the MySQL "slow queries" log, I could notice that there is an extremely slow query coming from WordPress, but I don’t know exactly which module:

SELECT SQL_CALC_FOUND_ROWS wpe_users.* FROM wpe_users WHERE 1=1 AND wpe_users.ID IN ( SELECT DISTINCT wpe_posts.post_author FROM wpe_posts WHERE wpe_posts.post_status = 'publish' AND wpe_posts.post_type IN ( 'post', 'page', 'attachment', 'wp_block', 'products' ) ) ORDER BY display_name ASC LIMIT 0, 10; 

The query appears two or more times, with State Copying to tmp table and Time of up to 53815.44 (secs? 15 hours? it’s crazy!). And it makes the Mysql process consume 557% of CPU and 8.7% of RAM

Server Error and Mysql Process List

The wpe_users table contains almost 3 million entries and the wpe_posts table contains 68,000 entries. The wpe_comments table is also starting to cause problems as it contains 361,000 entries.

I use pagination for comments and posts but not for users. The question is, how can I fix the problem without disabling paging? How can I identify where the user query is coming from if I don’t show users anywhere?

What have I tried?

  • I updated WordPress to the latest version available.
  • I tried to identify where these queries come from using the "Query Monitor" plugin but they don’t appear.
  • I verified the only 3 plugins that I have active and none of them generate such queries.
  • I closed the administration panel and killed said processes to see if the queries were generated by the administration panel but they start again…

I don’t have much experience with MySQL configuration. I am using MySQL 5.5.62 with Plesk panel on a VPS 5GB RAM 6 Cores

VERY strange issue with Instagram

There's a strange bug I've been hitting lately on Instagram.
Certain mutual followers not showing up, or very far down the list.

Say there is an account @x that is followed by a close friend of mine (@y). I follow @y and @y follows me back. It doesn't matter if I follow @x or not.

Before, when I would open @x's page, I would see "Followed by @y, @z + 42 more". But that no longer happens. For some reason @y is not in this short list of mutual followers. Also, when I open the full list of…

VERY strange issue with Instagram

Spatial Query very slow with radius

I posted this before at Query very slow when using spatial with radius which has a lot of details about my problem but I think i didnt include enough data so I am trying here again with database and the query I am having problem tuning.

Download database and attach Database [SQL Server 2019] (I promise no viruses, its just a .bak file in a zip), also scrubbed it out of info i dont want it out there 🙂 https://1drv.ms/u/s!AuxopE3ug8yWm-QTvSxyiHGIrAlXow?e=R7m20G

I shrank the database so its smaller to download, so you must run the following script to rebuild all indexes

EXECUTE sp_msForEachTable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD;' 

Run query (Non-Indexed View)

DECLARE @p3 sys.geography SET @p3=convert(sys.geography,0xE6100000010C010000209DE44540FFFFFF3F77CA53C0) SELECT l.* FROM GridListings l WHERE  l.Location.STDistance(@p3) < 15000 ORDER BY createddate OFFSET 0 ROWS FETCH NEXT 21 ROWS ONLY 

Or Indexed View

DECLARE @p3 sys.geography SET @p3=convert(sys.geography,0xE6100000010C010000209DE44540FFFFFF3F77CA53C0) SELECT l.* FROM GridListingsIndexed l WHERE  l.Location.STDistance(@p3) < 15000 ORDER BY createddate OFFSET 0 ROWS FETCH NEXT 21 ROWS ONLY 

What I am looking for (I am sorry if it is too much, but I am really desperate for help as lot of my queries are timing out on my app which some take between 6-50 seconds on a server with 32gb ram and 6 vcores (hyper v), the server also does other things but I think there is enough horse power

  1. I use the view above which already has non-expired listings filtered, then I use that view to filter down further listings but right now its slow with expirydate set in view and the radius against the view

  2. Look through my indexes and propose better indexes, improvement suggestions overall.

  3. If all fails, i might have to restore to separating my expired and non expired listings into separate tables, but this becomes a nightmare for maintenance

Balancing Encounters around a very proficient Necromancer

In a game I am a part of there is a player who considers themself a necromancer. They are fully specced and invested around the spell "Animate Dead". They are very effective and efficient at keeping track of, and controlling their minions, often taking a minute or few for their full turn with 7 fielded minions, each with multiple attacks. The problem is that they are really really powerful. Their control/raise limit is well over what it should be for their level (+9 from a mixture of class abilities and feats/traits that raise CL.) This means they have been able to raise anything that we have come across as at least a bloody skeleton, even adding extra template via use of desecrate. They also pair this with an elevated CL Command undead spell, giving them a large swath of minions to use that are hard to permanently kill.

The setting of the campaign has been the deep darklands, and while we are only level 7, they have given raise to 15HD bloody Firey skeletons. Needless to say, these outperform any martial at the same level.

The party is a small group, with 1 other consistent player who is a Rouge/Arcane trickster, (about as well optimized as they can be with a couple boons to make them stronger) and then a guest player every now and then who is less important and not consistent. As a GM it is getting hard to balance encounters because as the opponents scale up, the party does as well, but the characters do not gain extra capability. No one in the party is upset at the level of power they have, as they generally tend to do different rolls then meat-bag front-line. However they still like to feel useful in combat, and if enemies are too scaled up, they cant hurt them easily.

What are some ways to allow for more balanced encounters? The challenge is providing something that can be a threat to creatures of that capability, while still proving to be a threat to the rest of the party. Im not looking to nerf them unless absolutely necessary. The necromancer has his body hidden inside the skeleton of a large creature, strapped, secured, and hidden, while using shadow projection to command and be safer on the battlefield. AOEs can threaten his body, but its hard to justify direct targeting of his body over the skeleton it resides in, which is also quite mobile. Keep in mind, that the necromancer can raise pretty much anything, so after every encounter, they can potentially get stronger, and can at this point raise (7+5CL x2 = 24 x2 from desecrate = 48) a 24 HD creature as a bloody skeleton, or a 12 HD creature as a bloody flaming (or elemental) skeleton. This is at level 7.

Quick Question, How do I deal with this VERY ANNOYING Teammate

So I am playing with some friends over through discord, and one of them (I will call him bob) makes characters with a bit of a light temper, not that bad right?

Well, you’re wrong, if you do something or say something he doesn’t like, he makes his character slash you with his greatsword, it happened in multiple campaigns now, to my wizard (WHO ALMOST FAILED HIS DEATH SAVES BECAUSE HE ALMOST KILLED HIM) and to my bard. He’s a great friend of mine but how do I make him stop just slicing every teammate up that doesn’t agree with him?

Thank you all in advance.

Indexing very similar character varying field in postgres

I have a table with a column "name", the table is describing a media asset and the name is a character varying field containing the file name. The table is generated and used by a CMS (Strapi) and I can’t really tweak how the columns are used nor the SQL being executed. What I’m hoping to do is slap on an index (or two) and get a bit better performance.

The file names of our files are very similar, pretty much XYZ12345-Q2.png, where XYZ is the same for about 80% of the files. So what I’m wondering is what kind of index (if any) would help speeding up a query such as:

select count(*) as "count" from "upload_file" where ("upload_file"."name"::text ILIKE '%some_string%' or "upload_file"."id"::text ILIKE '%some_string%' 

The id is the primary key, and it’s an auto incrementing positive integer.

My concern regarding the actual string is that an index won’t do much when the file names are so similar? Or would it actually make a difference? In that case what would be the best index type to use? My understanding of Gin is that it wouldn’t really suit this case because there are no words (none of the file names contain space).

What are good options for a frontline melee with very limited source material?

I recently entered a Planescape campaign with a very rules heavy DM. I rolled stats in order, and now I have to make a character. As long as it stays within these sourcebooks and is neither a druid nor a full cleric (which my party already has) I’m open to options. Here’s the situation:

  • We are currently level 13, no LA buyoff possible.

  • I have 17 str, 15 Dex, 13 Con, 15 Int 15 Wis and 13 Cha.

  • I want to fill the role of a tank/melee combat controller. (I was considering maybe a trip oriented build?)

I have these options available:

  • PHB I
  • DMG I
  • MM I
  • Book of Exalted Deeds
  • Planar Guidebook
  • Tome of Magic
  • Guidebook to the Planetouched