We recently added MariaDB as a storage option for our tool. Before that we only allowed sqlite. Among other tables I have a very simple temporary file storage table:

create table tmp_file_storage
(
    uuid          varchar(255) not null,
    creation_date datetime     not null,
    filename      varchar(255) null,
    data_type     varchar(255) null,
    file          longblob     null
)

In production, writing a single entry into this table with a 25MB file (even if the table is empty), takes about 8 minutes. On my development PC it takes about 2 minutes, which is already way too slow for such a small file. Writing the data currently happens in 2 steps:

INSERT INTO tmp_file_storage(uuid, creation_date, filename, data_type, file)
VALUES ('someuuid', 'YYYY-MM-DD hh:mm:ss', NULL, NULL, NULL);

UPDATE TABLE tmp_file_storage
SET
  filename='filename',
  data_type='xml',
  file='.... 25MB of data ...';
WHERE
  tmp_file_storage.uuid = 'someuuid'

First we thought it could be something about our code, but we were able to reproduce this problem with just sending those queries to MariaDB. I tried using different Storage Engines (InnoDB, MyISAM, Aria) and it always took pretty much the same time. Here is the my.ini in case that matters. We haven't changed a lot of stuff.

[mysqld]
datadir=D:/.../data
innodb_lock_wait_timeout = 120
innodb_log_file_size     = 512M
innodb_log_buffer_size   = 128M
innodb_buffer_pool_size  = 1G
max_allowed_packet       = 500M

The database runs on the same server, and doesn't have a lot of load. Maybe around 10-100 requests a minute. With 25MB already taking so long, something seems to be actively slowing MariaDB down. In sqlite this operation usually takes less than a second. Any ideas?

I am executing a simple select query similar to:

DBI::dbGetQuery(db, "SELECT date, x, y, FROM table")

where the table contains a large amount of financial data. The db is a MS SQL server that I manage. I noticed slow query times so dug into the issue more carefully. I identified that these queries were generating large network wait times, specifically ASYNC_NETWORK_IO. The reason that I'm trying to improve the performance is that I often access financial data from a database I do not manage that uses PostgreSQL. I've put together a benchmark piece of code and for the same number of rows, columns, and column types, the same query runs 25-50% faster there. I'm a new database administrator so I'm trying to understand how to improve performance to approach the performance of this other (more professionally) managed database. From reading online (e.g., https://www.sqlshack.com/reducing-sql-server-async_network_io-wait-type/) , my prior is that this is a **application-caused** bottleneck in that I'm not reading the rows from the server fast enough. However, given that a similar piece of code executes much faster with another server, I'm still not sure that it's entirely the application's fault. Finally, if it is the application's fault, I'm having trouble finding resources that **specifically explain how to "read data more quickly"**. I have tried chunking my queries but that hasn't improved performance. For example, resources often say avoid "row by agonizing row" reads or try to "use the read immediately and process afterwards method". However, in my specific situation, I'm not sure what that means or how to avoid it. Thanks in advance for helping a novice DBA.

I'm running MySql5.7 with slow query logging enabled, and the log includes more metadata than is mentioned by the docs; these three lines are printed before each query: # Time: 2025-02-18T14:25:16.286041Z # User@Host: root[root] @ localhost [] Id: 261083 # Query_time: 3.677193 Lock_time: 0.001193 Rows_sent: 1 Rows_examined: 4334365 The docs explain that third line only: > each statement written to the log is preceded by a line that begins with a # character and has these fields (with all fields on a single line): > * Query_time > * Lock_time > * Rows_sent > * Rows_examined Of the other two lines, most of the fields are obvious; but does anybody know what the **Id** field represents? It is different for most of the entries in my slow log, but in some cases there are several which have the same Id.

I have a huge table that has fields ip, mac, and timestamp. Neither of the three fields is unique, but the combination of all three is. Table is automatically populated, with newer records added all the time. The field timestamp refers to when a row was added. Records are never *UPDATE*d. Here's the table description: Column | Type | Nullable | Default -----------+-----------------------------+----------+-------- event | text | not null | ip | inet | not null | mac | macaddr8 | not null | timestamp | timestamp without time zone | not null | now() Indexes: "ip_idx" btree (ip) "mac_idx" btree (mac) "time_idx" btree ("timestamp") "timestamp_ip_event_key" UNIQUE CONSTRAINT, btree ("timestamp", ip, event) I have this very slow query, causing the website to take very long time to load How can I speed it up? Is it possible to take advantage of the fact that the table is basically ordered by timestamp? I do not have access to the script that adds records. Executed SQL select ip, max(timestamp) from my_table WHERE ip Gather Merge (cost=291919.94..292169.16 rows=2136 width=15) (actual time=696.220..704.558 rows=429 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (cost=290919.92..290922.59 rows=1068 width=15) (actual time=679.313..679.325 rows=143 loops=3) Sort Key: ip Sort Method: quicksort Memory: 31kB Worker 0: Sort Method: quicksort Memory: 31kB Worker 1: Sort Method: quicksort Memory: 31kB -> Partial HashAggregate (cost=290855.52..290866.20 rows=1068 width=15) (actual time=679.192..679.233 rows=143 loops=3) Group Key: ip Batches: 1 Memory Usage: 81kB Worker 0: Batches: 1 Memory Usage: 81kB Worker 1: Batches: 1 Memory Usage: 81kB -> Parallel Bitmap Heap Scan on my_table (cost=12023.68..289019.89 rows=367126 width=15) (actual time=67.898..580.432 rows=312819 loops=3) Filter: (ip Bitmap Index Scan on my_table_ip_idx (cost=0.00..11803.41 rows=881097 width=0) (actual time=62.721..62.721 rows=938457 loops=1) Index Cond: ((ip > '10.38.69.0/24'::inet) AND (ip <= '10.38.69.255'::inet)) Planning Time: 1.049 ms JIT: Functions: 30 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 2.180 ms, Inlining 0.000 ms, Optimization 1.470 ms, Emission 29.303 ms, Total 32.952 ms Execution Time: 726.126 ms

The documentation does not seem to contain the word "encoding", so I am looking for a reference on the encoding of the MySQL slow query log. Based on the slow query logs I have seen so far, my current guess is that it is ASCII rather than UTF-8, but there are also strange characters that look like raw bytes for binary columns, so I am wondering if anyone knows what the intended encoding is, and whether it is documented anywhere.

I have a database of sufficient size that it does not fit entirely in RAM, including indexes that also exceed RAM capacity. When performing queries, I observe significant differences in processing time depending on whether the index needs to be read from disk or is already loaded in RAM. I have confirmed using EXPLAIN ANALYZE that the issue stems from index scans. See for example : * https://explain.dalibo.com/plan/2c85077gagh98a17 : very slow because some part of the index is "read" (from disk) and not "hit". * https://explain.dalibo.com/plan/gfd20f8cadaa5261#plan/node/8 : instantaneous, when everything is in RAM. I measured the speed of loading my index into RAM during a query, which is approximately 2 MB/s. However, my infrastructure theoretically supports disk read speeds of around 900 MB/s. This issue appears related to the index itself rather than a disk read speed cap. For instance, when I execute 2 parallel queries on different tables, the disk read speed reaches 4 MB/s. Yet, when I execute 2 parallel queries on the same table, my disk read remains at 2 MB/s. My question is : what can I change to reach an index reading speed from disk of 900 Mo/s ? I am working within an Azure VM environment. If additional information is required, I am available to provide it. Environment : * P80 disks * Postgresql 12 * VM E16s_v3 The partitioned table mentioned in the first plan more or less follows this DDL

-- public."F_TDLJ_HIST_1" definition

-- Drop table

-- DROP TABLE public."F_TDLJ_HIST_1";

CREATE TABLE public."F_TDLJ_HIST_1" (
	"ID_TRAIN" int4 NOT NULL,
	"ID_JOUR" int4 NOT NULL,
	"ID_LEG" int4 NOT NULL,
	"JX" int4 NOT NULL,
	"RES" int4 NULL,
	"REV" float8 NULL,
	"CAPA" int4 NULL,
	"OFFRE" int4 NULL,
	CONSTRAINT "F_TDLJ_HIST_1_OLDP_pkey" PRIMARY KEY ("ID_TRAIN", "ID_JOUR", "ID_LEG", "JX")
)
PARTITION BY RANGE ("ID_JOUR");
CREATE INDEX "F_TDLJ_HIST_1_OLDP_ID_JOUR_JX_idx" ON ONLY public."F_TDLJ_HIST_1" USING btree ("ID_JOUR", "JX");
CREATE INDEX "F_TDLJ_HIST_1_OLDP_ID_JOUR_idx" ON ONLY public."F_TDLJ_HIST_1" USING btree ("ID_JOUR");
CREATE INDEX "F_TDLJ_HIST_1_OLDP_ID_LEG_idx" ON ONLY public."F_TDLJ_HIST_1" USING btree ("ID_LEG");
CREATE INDEX "F_TDLJ_HIST_1_OLDP_ID_TRAIN_idx" ON ONLY public."F_TDLJ_HIST_1" USING btree ("ID_TRAIN");
CREATE INDEX "F_TDLJ_HIST_1_OLDP_JX_idx" ON ONLY public."F_TDLJ_HIST_1" USING btree ("JX");


-- public."F_TDLJ_HIST_1" foreign keys

ALTER TABLE public."F_TDLJ_HIST_1" ADD CONSTRAINT "F_TDLJ_HIST_1_OLDP_ID_JOUR_fkey" FOREIGN KEY ("ID_JOUR") REFERENCES public."D_JOUR"("ID_JOUR");
ALTER TABLE public."F_TDLJ_HIST_1" ADD CONSTRAINT "F_TDLJ_HIST_1_OLDP_ID_LEG_fkey" FOREIGN KEY ("ID_LEG") REFERENCES public."D_OD"("ID_OD");
ALTER TABLE public."F_TDLJ_HIST_1" ADD CONSTRAINT "F_TDLJ_HIST_1_OLDP_ID_TRAIN_fkey" FOREIGN KEY ("ID_TRAIN") REFERENCES public."D_TRAIN"("ID_TRAIN");
ALTER TABLE public."F_TDLJ_HIST_1" ADD CONSTRAINT "F_TDLJ_HIST_1_OLDP_JX_fkey" FOREIGN KEY ("JX") REFERENCES public."D_JX"("JX");

The first explain analyze plan is the "whole plan", I directly targeted a specific partition, under conditions on columns "ID_TRAIN", "ID_JOUR", "JX" Here is another query and plan. I am not directly selecting "ID_TRAIN", but the final index scan is the same: **300K via index scan, query:**

set track_io_timing=TRUE;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)(
    select "ID_TRAIN", "ID_JOUR", "JX", "ID_LEG", "RES", "REV" from "F_TDLJ_HIST" fth
    inner join "D_TRAIN" using ("ID_TRAIN")
    inner join "D_ENTNAT" using ("ID_ENTNAT")
    where "ENTITY" = 'LOIREPARIS' and "ID_JOUR" between 4770 and 4820 and "JX" between -92 and 1
);

**300K via index scan, plan:**

Nested Loop  (cost=2.63..18455.74 rows=44545 width=28) (actual time=51.034..645739.307 rows=304556 loops=1)
  Buffers: shared hit=86297 read=215554
  I/O Timings: read=642144.906
  ->  Nested Loop  (cost=2.07..60.13 rows=35 width=4) (actual time=0.128..1.656 rows=272 loops=1)
        Buffers: shared hit=71
        ->  Index Scan using "UX_ENTNAT" on "D_ENTNAT"  (cost=0.27..2.49 rows=1 width=4) (actual time=0.051..0.054 rows=1 loops=1)
              Index Cond: (("ENTITY")::text = 'LOIREPARIS'::text)
              Buffers: shared hit=3
        ->  Bitmap Heap Scan on "D_TRAIN"  (cost=1.80..57.11 rows=53 width=8) (actual time=0.073..1.356 rows=272 loops=1)
              Recheck Cond: ("ID_ENTNAT" = "D_ENTNAT"."ID_ENTNAT")
              Heap Blocks: exact=65
              Buffers: shared hit=68
              ->  Bitmap Index Scan on "fki_D_TRAIN_ID_ENTNAT_fkey"  (cost=0.00..1.78 rows=53 width=0) (actual time=0.037..0.037 rows=272 loops=1)
                    Index Cond: ("ID_ENTNAT" = "D_ENTNAT"."ID_ENTNAT")
                    Buffers: shared hit=3
  ->  Index Scan using "F_TDLJ_HIST_p4770_pkey" on "F_TDLJ_HIST_p4770" fth  (cost=0.56..436.98 rows=8861 width=28) (actual time=3.560..2373.034 rows=1120 loops=272)
        Index Cond: (("ID_TRAIN" = "D_TRAIN"."ID_TRAIN") AND ("ID_JOUR" >= 4770) AND ("ID_JOUR" = '-92'::integer) AND ("JX"   Hash Join  (cost=950.71..6668739.11 rows=15881782 width=32) (actual time=13.909..760404.410 rows=17267213 loops=1)
        Hash Cond: (fth."ID_TRAIN" = "D_TRAIN"."ID_TRAIN")
        Buffers: shared hit=252180 read=5575770
        I/O Timings: read=730384.999
        ->  Seq Scan on "F_TDLJ_HIST_1_OLDP_p4770" fth  (cost=0.00..6626086.32 rows=15881782 width=28) (actual time=6.084..754077.855 rows=17267213 loops=1)
              Filter: (("ID_JOUR" >= 4770) AND ("ID_JOUR" = '-92'::integer) AND ("JX"   Hash  (cost=772.54..772.54 rows=14254 width=8) (actual time=7.688..7.690 rows=14254 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 685kB
              Buffers: shared hit=630
              ->  Seq Scan on "D_TRAIN"  (cost=0.00..772.54 rows=14254 width=8) (actual time=0.069..5.492 rows=14254 loops=1)
                    Buffers: shared hit=630
  ->  Hash  (cost=19.59..19.59 rows=408 width=4) (actual time=229.387..229.388 rows=408 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 23kB
        Buffers: shared hit=12
        ->  Index Only Scan using "D_ENTNAT_pkey1" on "D_ENTNAT"  (cost=0.27..19.59 rows=408 width=4) (actual time=0.019..0.190 rows=408 loops=1)
              Heap Fetches: 0
              Buffers: shared hit=12
Settings: effective_cache_size = '96GB', effective_io_concurrency = '200', max_parallel_workers_per_gather = '4', random_page_cost = '1.1', search_path = 'public', work_mem = '64MB'
Planning Time: 3.351 ms
JIT:
  Functions: 17
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.732 ms, Inlining 62.117 ms, Optimization 97.389 ms, Emission 69.264 ms, Total 230.502 ms
Execution Time: 765721.071 ms

**20M via seq scan, query:**

set track_io_timing=TRUE; 
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)(
    select "ID_TRAIN", "ID_JOUR", "JX", "ID_LEG", "RES", "REV" from "F_TDLJ_HIST_1" fth
    inner join "D_TRAIN" using ("ID_TRAIN")
    inner join "D_ENTNAT" using ("ID_ENTNAT")
    where "ID_JOUR" between 4770 and 4820 and "JX" between -92 and 1
);

**20M via seq scan, plan:**

Hash Join  (cost=975.41..6710886.53 rows=15881782 width=28) (actual time=243.368..764675.784 rows=17267213 loops=1)
  Hash Cond: ("D_TRAIN"."ID_ENTNAT" = "D_ENTNAT"."ID_ENTNAT")
  Buffers: shared hit=252195 read=5575770
  I/O Timings: read=730384.999
  ->  Hash Join  (cost=950.71..6668739.11 rows=15881782 width=32) (actual time=13.909..760404.410 rows=17267213 loops=1)
        Hash Cond: (fth."ID_TRAIN" = "D_TRAIN"."ID_TRAIN")
        Buffers: shared hit=252180 read=5575770
        I/O Timings: read=730384.999
        ->  Seq Scan on "F_TDLJ_HIST_1_OLDP_p4770" fth  (cost=0.00..6626086.32 rows=15881782 width=28) (actual time=6.084..754077.855 rows=17267213 loops=1)
              Filter: (("ID_JOUR" >= 4770) AND ("ID_JOUR" = '-92'::integer) AND ("JX"   Hash  (cost=772.54..772.54 rows=14254 width=8) (actual time=7.688..7.690 rows=14254 loops=1)
              Buckets: 16384  Batches: 1  Memory Usage: 685kB
              Buffers: shared hit=630
              ->  Seq Scan on "D_TRAIN"  (cost=0.00..772.54 rows=14254 width=8) (actual time=0.069..5.492 rows=14254 loops=1)
                    Buffers: shared hit=630
  ->  Hash  (cost=19.59..19.59 rows=408 width=4) (actual time=229.387..229.388 rows=408 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 23kB
        Buffers: shared hit=12
        ->  Index Only Scan using "D_ENTNAT_pkey1" on "D_ENTNAT"  (cost=0.27..19.59 rows=408 width=4) (actual time=0.019..0.190 rows=408 loops=1)
              Heap Fetches: 0
              Buffers: shared hit=12
Settings: effective_cache_size = '96GB', effective_io_concurrency = '200', max_parallel_workers_per_gather = '4', random_page_cost = '1.1', search_path = 'public', work_mem = '64MB'
Planning Time: 3.351 ms
JIT:
  Functions: 17
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.732 ms, Inlining 62.117 ms, Optimization 97.389 ms, Emission 69.264 ms, Total 230.502 ms
Execution Time: 765721.071 ms

My system (OpenMage / Magento 1) is running some queries like the following and even though it's not using any large tables it seems that they take an abnormally high amount of time to execute. Sometimes they take a few minutes, sometimes they will timeout after a day. For example

# Time: 240405  7:37:19
# User@Host: db_example3[db_example3] @ localhost [127.0.0.1]
# Thread_id: 8345298  Schema: db_example3  QC_hit: No
# Query_time: 140.075668  Lock_time: 0.000176  Rows_sent: 1  Rows_examined: 15
# Rows_affected: 0  Bytes_sent: 148
# Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 253976
# Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
#
# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    SIMPLE  ram_idx ref     PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE     4       const   2       2.00    100.00  100.00  Using where; Using index; Using temporary; Using filesort
# explain: 1    SIMPLE  gdiktia_idx     ref     PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,idx_attribute_store_value_entity       IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID 8       db_example3.ram_idx.entity_id,const,const      1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  primary_camera_idx      range   PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE     4       NULL    2       2.00    75.00   25.00   Using where; Using index; Using join buffer (flat, BNL join)
# explain: 1    SIMPLE  screen_resolution_idx   eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  memory_idx      eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  second_camera_idx       eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  nfcsosto_idx    eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  price_index     eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID,IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE        PRIMARY  8       db_example3.ram_idx.entity_id,const,const      1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  core_count_idx  eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  megethossim_idx eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.ram_idx.entity_id,const,const,const        1       1.00    100.00  100.00  Using index
# explain: 1    SIMPLE  cat_index       eq_ref  PRIMARY,IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,15D3C269665C74C2219037D534F4B0DC        PRIMARY 10      const,db_example3.ram_idx.entity_id,const       1       1.00    100.00  100.00  Using where
# explain: 1    SIMPLE  e       eq_ref  PRIMARY PRIMARY 4       db_example3.ram_idx.entity_id  1       1.00    100.00  100.00  Using index
#
SET timestamp=1712291839;
SELECT gdiktia_idx.value, COUNT(gdiktia_idx.entity_id) AS count FROM catalog_product_entity AS e
 INNER JOIN catalog_category_product_index AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '17'
 INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 INNER JOIN catalog_product_index_eav AS screen_resolution_idx ON screen_resolution_idx.entity_id = e.entity_id AND screen_resolution_idx.attribute_id = 188 AND screen_resolution_idx.store_id = 1 AND screen_resolution_idx.value = '234'
 INNER JOIN catalog_product_index_eav AS core_count_idx ON core_count_idx.entity_id = e.entity_id AND core_count_idx.attribute_id = 193 AND core_count_idx.store_id = 1 AND core_count_idx.value = '41'
 INNER JOIN catalog_product_index_eav AS ram_idx ON ram_idx.entity_id = e.entity_id AND ram_idx.attribute_id = 196 AND ram_idx.store_id = 1 AND ram_idx.value = '54'
 INNER JOIN catalog_product_index_eav AS memory_idx ON memory_idx.entity_id = e.entity_id AND memory_idx.attribute_id = 197 AND memory_idx.store_id = 1 AND memory_idx.value = '62'
 INNER JOIN catalog_product_index_eav AS primary_camera_idx ON primary_camera_idx.entity_id = e.entity_id AND primary_camera_idx.attribute_id = 198 AND primary_camera_idx.store_id = 1 AND primary_camera_idx.value = '315'
 INNER JOIN catalog_product_index_eav AS second_camera_idx ON second_camera_idx.entity_id = e.entity_id AND second_camera_idx.attribute_id = 200 AND second_camera_idx.store_id = 1 AND second_camera_idx.value = '90'
 INNER JOIN catalog_product_index_eav AS megethossim_idx ON megethossim_idx.entity_id = e.entity_id AND megethossim_idx.attribute_id = 229 AND megethossim_idx.store_id = 1 AND megethossim_idx.value = '164'
 INNER JOIN catalog_product_index_eav AS nfcsosto_idx ON nfcsosto_idx.entity_id = e.entity_id AND nfcsosto_idx.attribute_id = 258 AND nfcsosto_idx.store_id = 1 AND nfcsosto_idx.value = '377'
 INNER JOIN catalog_product_index_eav AS gdiktia_idx ON gdiktia_idx.entity_id = e.entity_id AND gdiktia_idx.attribute_id = 232 AND gdiktia_idx.store_id = '1' GROUP BY gdiktia_idx.value;

or

# Time: 240405  7:34:29
# User@Host: db_example3[db_example3] @ localhost [127.0.0.1]
# Thread_id: 8344334  Schema: db_example3  QC_hit: No
# Query_time: 74.418149  Lock_time: 0.000100  Rows_sent: 0  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 142
# Tmp_tables: 1  Tmp_disk_tables: 0  Tmp_table_sizes: 0
# Full_scan: No  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No
# Filesort: Yes  Filesort_on_disk: No  Merge_passes: 0  Priority_queue: No
#
# explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
# explain: 1    SIMPLE  extra_specs_idx ref     PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,idx_attribute_store_value_entity       IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID      2       const   1       0.00    100.00  100.00  Using where; Using index; Using temporary; Using filesort
# explain: 1    SIMPLE  manufacturer2_idx       eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  primary_camera_idx      eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  second_camera_idx       eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  ram_idx eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  nfcsosto_idx    eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  operating_system_idx    eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  megethossim_idx eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   PRIMARY 12      db_example3.extra_specs_idx.entity_id,const,const,const        1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  price_index     eq_ref  PRIMARY,IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID,IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID,IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE        PRIMARY  8       db_example3.extra_specs_idx.entity_id,const,const      1       NULL    100.00  NULL    Using where
# explain: 1    SIMPLE  cat_index       eq_ref  PRIMARY,IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY,15D3C269665C74C2219037D534F4B0DC        PRIMARY 10      const,db_example3.extra_specs_idx.entity_id,const       1       NULL    100.00  NULL    Using where
# explain: 1    SIMPLE  e       eq_ref  PRIMARY PRIMARY 4       db_example3.extra_specs_idx.entity_id  1       NULL    100.00  NULL    Using index
# explain: 1    SIMPLE  screen_type_idx range   PRIMARY,IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID,IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE,idx_attribute_store_value_entity   IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE     4       NULL    2       NULL    75.00   NULL    Using where; Using index; Using join buffer (flat, BNL join)
#
SET timestamp=1712291669;
SELECT extra_specs_idx.value, COUNT(extra_specs_idx.entity_id) AS count FROM catalog_product_entity AS e
 INNER JOIN catalog_category_product_index AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id = '3'
 INNER JOIN catalog_product_index_price AS price_index ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 INNER JOIN catalog_product_index_eav AS manufacturer2_idx ON manufacturer2_idx.entity_id = e.entity_id AND manufacturer2_idx.attribute_id = 186 AND manufacturer2_idx.store_id = 1 AND manufacturer2_idx.value = '6'
 INNER JOIN catalog_product_index_eav AS screen_type_idx ON screen_type_idx.entity_id = e.entity_id AND screen_type_idx.attribute_id = 189 AND screen_type_idx.store_id = 1 AND screen_type_idx.value = '37'
 INNER JOIN catalog_product_index_eav AS operating_system_idx ON operating_system_idx.entity_id = e.entity_id AND operating_system_idx.attribute_id = 195 AND operating_system_idx.store_id = 1 AND operating_system_idx.value = '48'
 INNER JOIN catalog_product_index_eav AS primary_camera_idx ON primary_camera_idx.entity_id = e.entity_id AND primary_camera_idx.attribute_id = 198 AND primary_camera_idx.store_id = 1 AND primary_camera_idx.value = '309'
 INNER JOIN catalog_product_index_eav AS second_camera_idx ON second_camera_idx.entity_id = e.entity_id AND second_camera_idx.attribute_id = 200 AND second_camera_idx.store_id = 1 AND second_camera_idx.value = '87'
 INNER JOIN catalog_product_index_eav AS megethossim_idx ON megethossim_idx.entity_id = e.entity_id AND megethossim_idx.attribute_id = 229 AND megethossim_idx.store_id = 1 AND megethossim_idx.value = '164'
 INNER JOIN catalog_product_index_eav AS ram_idx ON ram_idx.entity_id = e.entity_id AND ram_idx.attribute_id = 196 AND ram_idx.store_id = 1 AND ram_idx.value = '52'
 INNER JOIN catalog_product_index_eav AS nfcsosto_idx ON nfcsosto_idx.entity_id = e.entity_id AND nfcsosto_idx.attribute_id = 258 AND nfcsosto_idx.store_id = 1 AND nfcsosto_idx.value = '377'
 INNER JOIN catalog_product_index_eav AS extra_specs_idx ON extra_specs_idx.entity_id = e.entity_id AND extra_specs_idx.attribute_id = 213 AND extra_specs_idx.store_id = '1' WHERE ((price_index.min_price -(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)+((price_index.min_price-(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)) >= 99.995000) AND ((price_index.min_price -(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)+((price_index.min_price-(price_index.min_price/(1+(CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END))*CASE price_index.tax_class_id WHEN 2 THEN       0.2400  ELSE 0 END)) < 199.995000) GROUP BY extra_specs_idx.value;

As far as I can see the tables are using indexes, and generally nothing is written on the disk, everything is happening in memory so I'm not sure how to optimize and make them run faster. My mariadb version is

Server version: 10.6.17-MariaDB-1:10.6.17+maria~ubu2004-log mariadb.org binary distribution

Here are the table definitions

create table db_example3.catalog_category_product_index
(
    category_id int unsigned      default 0 not null comment 'Category ID',
    product_id  int unsigned      default 0 not null comment 'Product ID',
    position    int                         null comment 'Position',
    is_parent   smallint unsigned default 0 not null comment 'Is Parent',
    store_id    smallint unsigned default 0 not null comment 'Store ID',
    visibility  smallint unsigned           not null comment 'Visibility',
    primary key (category_id, product_id, store_id),
    constraint FK_CATALOG_CATEGORY_PRODUCT_INDEX_STORE_ID_CORE_STORE_STORE_ID
        foreign key (store_id) references db_example3.core_store (store_id)
            on update cascade on delete cascade,
    constraint FK_CAT_CTGR_PRD_IDX_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID
        foreign key (category_id) references db_example3.catalog_category_entity (entity_id)
            on update cascade on delete cascade,
    constraint FK_CAT_CTGR_PRD_IDX_PRD_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (product_id) references db_example3.catalog_product_entity (entity_id)
            on update cascade on delete cascade
)
    comment 'Catalog Category Product Index' row_format = COMPRESSED;

create index 15D3C269665C74C2219037D534F4B0DC
    on db_example3.catalog_category_product_index (store_id, category_id, visibility, is_parent, position);

create index IDX_CAT_CTGR_PRD_IDX_PRD_ID_STORE_ID_CTGR_ID_VISIBILITY
    on db_example3.catalog_category_product_index (product_id, store_id, category_id, visibility);

create table db_example3.catalog_product_index_eav
(
    entity_id    int unsigned      not null comment 'Entity ID',
    attribute_id smallint unsigned not null comment 'Attribute ID',
    store_id     smallint unsigned not null comment 'Store ID',
    value        int unsigned      not null comment 'Value',
    primary key (entity_id, attribute_id, store_id, value),
    constraint FK_CATALOG_PRODUCT_INDEX_EAV_STORE_ID_CORE_STORE_STORE_ID
        foreign key (store_id) references db_example3.core_store (store_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_EAV_ATTR_ID_EAV_ATTR_ATTR_ID
        foreign key (attribute_id) references db_example3.eav_attribute (attribute_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_EAV_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references db_example3.catalog_product_entity (entity_id)
            on update cascade on delete cascade
)
    comment 'Catalog Product EAV Index Table' row_format = COMPRESSED;

create index IDX_CATALOG_PRODUCT_INDEX_EAV_ATTRIBUTE_ID
    on db_example3.catalog_product_index_eav (attribute_id);

create index IDX_CATALOG_PRODUCT_INDEX_EAV_ENTITY_ID
    on db_example3.catalog_product_index_eav (entity_id);

create index IDX_CATALOG_PRODUCT_INDEX_EAV_STORE_ID
    on db_example3.catalog_product_index_eav (store_id);

create index IDX_CATALOG_PRODUCT_INDEX_EAV_VALUE
    on db_example3.catalog_product_index_eav (value);

create index idx_attribute_store_value_entity
    on db_example3.catalog_product_index_eav (attribute_id, store_id, value, entity_id);

create table db_example3.catalog_product_index_price
(
    entity_id         int unsigned                not null comment 'Entity ID',
    customer_group_id smallint unsigned           not null comment 'Customer Group ID',
    website_id        smallint unsigned           not null comment 'Website ID',
    tax_class_id      smallint unsigned default 0 null comment 'Tax Class ID',
    price             decimal(12, 4)              null comment 'Price',
    final_price       decimal(12, 4)              null comment 'Final Price',
    min_price         decimal(12, 4)              null comment 'Min Price',
    max_price         decimal(12, 4)              null comment 'Max Price',
    tier_price        decimal(12, 4)              null comment 'Tier Price',
    group_price       decimal(12, 4)              null comment 'Group price',
    primary key (entity_id, customer_group_id, website_id),
    constraint FK_CAT_PRD_IDX_PRICE_CSTR_GROUP_ID_CSTR_GROUP_CSTR_GROUP_ID
        foreign key (customer_group_id) references db_example3.customer_group (customer_group_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_PRICE_ENTT_ID_CAT_PRD_ENTT_ENTT_ID
        foreign key (entity_id) references db_example3.catalog_product_entity (entity_id)
            on update cascade on delete cascade,
    constraint FK_CAT_PRD_IDX_PRICE_WS_ID_CORE_WS_WS_ID
        foreign key (website_id) references db_example3.core_website (website_id)
            on update cascade on delete cascade
)
    comment 'Catalog Product Price Index Table' row_format = COMPRESSED;

create index IDX_CATALOG_PRODUCT_INDEX_PRICE_CUSTOMER_GROUP_ID
    on db_example3.catalog_product_index_price (customer_group_id);

create index IDX_CATALOG_PRODUCT_INDEX_PRICE_MIN_PRICE
    on db_example3.catalog_product_index_price (min_price);

create index IDX_CATALOG_PRODUCT_INDEX_PRICE_WEBSITE_ID
    on db_example3.catalog_product_index_price (website_id);

create index IDX_CAT_PRD_IDX_PRICE_WS_ID_CSTR_GROUP_ID_MIN_PRICE
    on db_example3.catalog_product_index_price (website_id, customer_group_id, min_price);

The counted rows on each table

select count(*) from catalog_product_index_eav; #418
select count(*) from catalog_product_index_price; #84
select count(*) from catalog_category_product_index; # 314

The server has an AMD Ryzen 9 3900 12-Core Processor with 128GB of RAM and Nvme disks. Any help is appreciated

I've been trying to debug a particularly slow query that never completes (it takes forever and eventually timeout), and found out that it's down to the ORDER BY statement: if it's there, it never completes, if I remove it, it returns instantly. My assumption was that there was no index on that field, however I found out that there is one:

CREATE UNIQUE INDEX changes_pkey ON public.changes USING btree (counter)

However that doesn't seem to make any difference so I'm wondering what could be the reason? Is that perhaps because it's a "UNIQUE INDEX" unlike the other indexes on this table? Please see below for the queries: **Never complete:**

SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND (type = 1 OR type = 3)
AND user_id = 'xxxxxxx'
ORDER BY "counter" ASC
LIMIT 200

**Completes instantly:**

SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND (type = 1 OR type = 3)
AND user_id = 'xxxxxxx'
LIMIT 200

**Indexes on that table:**

changes              | changes_id_index                          | CREATE INDEX changes_id_index ON public.changes USING btree (id)
changes              | changes_id_unique                         | CREATE UNIQUE INDEX changes_id_unique ON public.changes USING btree (id)
changes              | changes_item_id_index                     | CREATE INDEX changes_item_id_index ON public.changes USING btree (item_id)
changes              | changes_pkey                              | CREATE UNIQUE INDEX changes_pkey ON public.changes USING btree (counter)
changes              | changes_user_id_index                     | CREATE INDEX changes_user_id_index ON public.changes USING btree (user_id)

postgres=> EXPLAIN SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
postgres-> FROM "changes"
postgres-> WHERE counter > -1
postgres-> AND (type = 1 OR type = 3)
postgres-> AND user_id = 'xxxxxxxx'
postgres-> ORDER BY "counter" ASC
postgres-> LIMIT 200;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..9206.44 rows=200 width=99)
   ->  Index Scan using changes_pkey on changes  (cost=0.56..5746031.01 rows=124834 width=99)
         Index Cond: (counter > '-1'::integer)
         Filter: (((user_id)::text = 'xxxxxxxx'::text) AND ((type = 1) OR (type = 3)))
(4 rows)

***EXPLAIN for the fast query:**

postgres=> EXPLAIN SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
postgres-> FROM "changes"
postgres-> WHERE counter > -1
postgres-> AND (type = 1 OR type = 3)
postgres-> AND user_id = 'xxxxxxxx'
postgres-> LIMIT 200;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..1190.09 rows=200 width=99)
   ->  Index Scan using changes_user_id_index on changes  (cost=0.56..742468.10 rows=124834 width=99)
         Index Cond: ((user_id)::text = 'xxxxxxxx'::text)
         Filter: ((counter > '-1'::integer) AND ((type = 1) OR (type = 3)))
(4 rows)

Any idea what could be the reason for this slow query?

Hope someone can assist me. I think I might know what the problem is but this is not really resolving the issue and was wondering if it is even a valid conclusion. I am not a Guru at all with SQL. Running windows sever standard 2019 OLD Server Specs: - 32 gigs ddr4 Dimm Ram - Intel i7-7700 3.6ghz cpu - 1 socket 4 cores / 8 Threads - 1T SSD New Server windows server 2019 date center - 256 Gig DDR4 dimm Ram - Xeon E5 2699 v4 2.2ghz cpu - 2 x sockets 22 x 2 cores =44 /88 Threads - 4x 1.8T Sas Drives Raid 5 (backups) - 5x 1T evo Sata SSD tried raid5 and raid10 (SQL DAtaBase) - 2x 1T samsung evo Nvme Tried mirrored and normal (Windows install) Vms was given a lot and the same quantity of ram then old server bur no improvement. Currently we are running 3 Vms - vm1 AD-DC - vm2 SQL-Database and IIS - vm3 RDS Terminal Working with 4 x 10 gig datebases since sql express is limited to 10gig. Storage configuration - Vm1 AD-DC 1 x V-H drive image stored on (windows drive nvme) 50Gig Storage allocated - vm2 1 x V-H drive for the os 120GIG and 1 x V-h drive for sql data on the raid 10 and raid 5 tested both Samsung evo drives (2.8T storage allocated) - Vm3 - RDS Terminal V-H drive Iso on Sql raid Samsung evo Drive 100gig The VM storage and Virtual h-drives was changed around to test if it might be the raid controller or a drive which was causing the slow responds on retrieving data from the Database. The old and new server are setup the same with the 3 vms same V-h drive configuration and drives used but when trying to load the application we use to access the data It takes forever almost 30 seconds longer and all of the actions when loaded in you click and wait 10 seconds on the new server. Even when all of the VMs and all of the storage are on one physical drive no performance difference. All of the VMS and Host is set to best performance and all of the drivers are installed as per previous discussions. Tried giving all of the VMs double the memory and processors even the SQL VM No difference. **Theory** My Suspicion If it is valid and that is what i want to know is Since SQL Express is limited to 1 gig of memory and 4 processors it does not matter if i give it more but the clock speed between the 2 servers are much different so comparing 4x 2.2ghz with 4 x 3.7ghz The old server will be faster on the SQL EXPRESS in till we upgrade it to the full version where i can assign more processors? Hope this make sense and that i did not leave anything out but if someone can give some advice it will be greatly appreciated. Is the Theory valid?

I have encountered a significant performance issue when executing the 'DELETE' operation on a PostgreSQL database table. The execution time for deleting 15488 records was 79423.768 ms which is very slow compared to other operations like 'INSERT' or 'SELECT'. I would appreciate any insights or suggestions on why this might be happening and possible approaches to optimize the delete operation. Background: I am using PostgreSQL Engine version 12.14 as the backend for my application, and I have noticed that deleting records from one table takes an unexpectedly long time. The tables involved have indexes and constraints defined, and the database size is relatively small, expected to grow upto a few GBs. However, the issue appears to be more pronounced for this specific table, while others perform adequately. **Hardware** is AWS db.t2.micro instance with 1 cpu core, 1 (GiB) of memory and 20 (GiB) of General Purpose SSD for storage. column_name_loading Table schema, the table we trying to delete from. | Column Name | Data Type | Description | |-------------|-----------|-------------| | id | TEXT | Primary key | | hash | TEXT | Primary key | | date_from | TIMESTAMP | Primary key | | date_to | TIMESTAMP | | | measurement_location_uuid| UUID | Primary Key, Foreign key | | column_name | TEXT | Not null | | statistic_type_id | TEXT | | | is_ignored | BOOLEAN | | | notes | TEXT | | | update_at | TIMESTAMP | | | updated_by| UUID | | As you can see, the above table has a composite primary key involving 4 columns. There are two tables that are having a foreign key reference to the column_name_loading table First table

ALTER  TABLE
logger_main_config_column_name_loading 
ADD
CONSTRAINT column_name_loading_fkey FOREIGN KEY (
column_name_loading_measurement_location_uuid,
column_name_loading_id,
column_name_loading_hash,
column_name_loading_date_from
) REFERENCES column_name_loading(measurement_location_uuid, id, hash, date_from);

Second table

ALTER  TABLE
logger_measurement_config_column_name_loading
ADD
CONSTRAINT column_name_loading_fkey FOREIGN KEY (
column_name_loading_measurement_location_uuid,
column_name_loading_id,
column_name_loading_hash,
column_name_loading_date_from
) REFERENCES column_name_loading(measurement_location_uuid, id, hash, date_from);

The measurement_location_location_uuid foreign keys in the above tables refer to the same table that the column_name_loading refers to. #### Delete query

DELETE FROM column_name_loading WHERE measurement_location_uuid='7f925e5c-3d34-417e-8782-052a69692b2b'

#### Postgres Query Analysis

"Delete on column_name_loading  (cost=0.00..1232.60 rows=15476 width=6) (actual time=44.797..44.801 rows=0 loops=1)"
"  Buffers: shared hit=31799 dirtied=462"
"  ->  Seq Scan on column_name_loading  (cost=0.00..1232.60 rows=15476 width=6) (actual time=0.016..16.843 rows=15488 loops=1)"
"        Filter: (measurement_location_uuid = 'ed67b48b-c48a-4727-87cd-5a5f4d27fa7a'::uuid)"
"        Rows Removed by Filter: 17280"
"        Buffers: shared hit=823"
"Planning Time: 0.103 ms"
"Trigger for constraint column_name_loading_fkey: time=39562.957 calls=15488"
"Trigger for constraint column_name_loading_fkey: time=39759.667 calls=15488"
"Execution Time: 79423.768 ms"

The column_name_loading table can have a few million records in future and also the tables that refer to it through foreign keys. We want to be able to do select and delete operations in a few minutes at least. #### Things we tried - Dropping the cascade delete on measurement_location_uuid foreign key and we had no performance improvement - Dropping the cascade delete on the two foreign keys referencing the column_name_loading table and we had no performance improvement - Created an index on measurement_location_uuid foreign key, showed some improvement but the delete for those many records still causes a timeout. We have also referred to this question for some insights and tried the indexing and dropping cascade delete. https://dba.stackexchange.com/questions/37034/very-slow-delete-in-postgresql-workaround I would really appreciate some help on the following: 1. Why is the 'DELETE' operation on certain tables in my PostgreSQL database noticeably slower compared to other operations? 2. Are there any factors within the database schema or configuration that might contribute to this slow performance? 3. What strategies or optimizations can I implement to improve the efficiency and speed of the 'DELETE' operation on these tables? Please note that I have already examined the query execution plans, and there doesn't appear to be any obvious bottlenecks or long-running queries causing the delay. Additionally, I have ensured that all relevant indexes are properly maintained and up to date. I would appreciate any guidance or suggestions on how to troubleshoot and optimize the 'DELETE' operation on PostgreSQL database tables. Thank you! **EDIT:** On trying the indexes mentioned in the answer below, there was some improvement in the delete from column_name_loading query. Following are the results: "Delete on column_name_loading (cost=176.99..1313.87 rows=0 width=0) (actual time=276.497..276.498 rows=0 loops=1)" " -> Bitmap Heap Scan on column_name_loading (cost=176.99..1313.87 rows=13510 width=6) (actual time=0.596..100.690 rows=12800 loops=1)" " Recheck Cond: (measurement_location_uuid = '68dd4fae-c2bf-413d-ba3c-cb63b062307f'::uuid)" " Heap Blocks: exact=334" " -> Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid (cost=0.00..173.61 rows=13510 width=0) (actual time=0.280..0.280 rows=12800 loops=1)" " Index Cond: (measurement_location_uuid = '68dd4fae-c2bf-413d-ba3c-cb63b062307f'::uuid)" "Planning Time: 0.066 ms" "Trigger for constraint column_name_loading_fkey: time=256.829 calls=12800" "Trigger for constraint column_name_loading_fkey: time=243.210 calls=12800" "Execution Time: 778.337 ms" On trying with more number of records in the column_name_loading (102,528) and the logger_measurement_config_column_name_loading table: "Delete on column_name_loading (cost=317.62..3212.88 rows=0 width=0) (actual time=260.420..260.421 rows=0 loops=1)" " -> Bitmap Heap Scan on column_name_loading (cost=317.62..3212.88 rows=25461 width=6) (actual time=2.778..11.249 rows=25600 loops=1)" " Recheck Cond: (measurement_location_uuid = 'd6f9d978-20e0-49a1-a6fc-cad0865500d9'::uuid)" " Heap Blocks: exact=657" " -> Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid (cost=0.00..311.25 rows=25461 width=0) (actual time=2.663..2.664 rows=25600 loops=1)" " Index Cond: (measurement_location_uuid = 'd6f9d978-20e0-49a1-a6fc-cad0865500d9'::uuid)" "Planning Time: 7.639 ms" "Trigger for constraint column_name_loading_fkey: time=881.762 calls=25600" "Trigger for constraint column_name_loading_fkey: time=659.424 calls=25600" "Execution Time: 1806.113 ms" "Delete on column_name_loading (cost=474.61..5140.76 rows=0 width=0) (actual time=622.069..622.071 rows=0 loops=1)" " -> Bitmap Heap Scan on column_name_loading (cost=474.61..5140.76 rows=38492 width=6) (actual time=66.150..89.596 rows=38400 loops=1)" " Recheck Cond: (measurement_location_uuid = '4736f9df-3d53-4896-bc72-e48a118bbfab'::uuid)" " Heap Blocks: exact=1004" " -> Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid (cost=0.00..464.99 rows=38492 width=0) (actual time=65.912..65.913 rows=38400 loops=1)" " Index Cond: (measurement_location_uuid = '4736f9df-3d53-4896-bc72-e48a118bbfab'::uuid)" "Planning Time: 54.825 ms" "Trigger for constraint column_name_loading_fkey: time=1118.807 calls=38400" "Trigger for constraint column_name_loading_fkey: time=805.790 calls=38400" "Execution Time: 2553.990 ms" "Delete on column_name_loading (cost=815.01..9706.90 rows=0 width=0) (actual time=693.918..693.919 rows=0 loops=1)" " -> Bitmap Heap Scan on column_name_loading (cost=815.01..9706.90 rows=67431 width=6) (actual time=10.708..212.115 rows=64000 loops=1)" " Recheck Cond: (measurement_location_uuid = 'f902ce5b-baf8-4260-bad7-83df2a283a0f'::uuid)" " Heap Blocks: exact=1614" " -> Bitmap Index Scan on idx_column_name_loading_measurement_location_uuid (cost=0.00..798.15 rows=67431 width=0) (actual time=10.024..10.024 rows=64000 loops=1)" " Index Cond: (measurement_location_uuid = 'f902ce5b-baf8-4260-bad7-83df2a283a0f'::uuid)" "Planning Time: 7.719 ms" "Trigger for constraint column_name_loading_fkey: time=1727.786 calls=64000" "Trigger for constraint column_name_loading_fkey: time=1275.343 calls=64000" "Execution Time: 3707.070 ms" The queries run faster, if the cascade delete is removed and the index is added on the entire foreign key. However, it still took roughly 2 seconds to delete a few records from 100,000 records and it only increases as the number of records increases. Around 4 seconds to delete a few thousand records from around 400,000 records. Is that speed normal? This table can have up to 9-10 million records. Now imagine the time it would take to delete a few thousand records from this table.