Tag: slower

Why is OR statement slower than UNION

Database version: Postgresql 12.6

I have a table with 600000 records.

The table has columns:

  • name (varchar)
  • location_type (int) enum values: (1,2,3)
  • ancestry (varchar)

Indexes:

  • ancestry (btree)

The ancestry column is a way to build a tree where every row has an ancestry containing all parent ids separated by ‘/’

Consider the following example:

id name ancestry
1 root null
5 node ‘1’
12 node ‘1/5’
22 leaf ‘1/5/12’

The following query takes 686 ms to execute:

SELECT * FROM geolocations WHERE EXISTS (    SELECT 1 FROM geolocations g2    WHERE g2.ancestry =        CONCAT(geolocations.ancestry, '/', geolocations.id) )

This query runs in 808 ms seconds:

SELECT * FROM geolocations WHERE location_type = 2

When combining both queried with an OR it takes around 4 seconds 475 ms to finish if it ever finishes.

SELECT * FROM geolocations WHERE EXISTS (    SELECT 1 FROM geolocations g2    WHERE g2.ancestry =        CONCAT(geolocations.ancestry, '/', geolocations.id) ) OR location_type = 2

Explain:

[   {     "Plan": {       "Node Type": "Seq Scan",       "Parallel Aware": false,       "Relation Name": "geolocations",       "Alias": "geolocations",       "Startup Cost": 0,       "Total Cost": 2760473.54,       "Plan Rows": 582910,       "Plan Width": 68,       "Filter": "((SubPlan 1) OR (location_type = 2))",       "Plans": [         {           "Node Type": "Index Only Scan",           "Parent Relationship": "SubPlan",           "Subplan Name": "SubPlan 1",           "Parallel Aware": false,           "Scan Direction": "Forward",           "Index Name": "index_geolocations_on_ancestry",           "Relation Name": "geolocations",           "Alias": "g2",           "Startup Cost": 0.43,           "Total Cost": 124.91,           "Plan Rows": 30,           "Plan Width": 0,           "Index Cond": "(ancestry = concat(geolocations.ancestry, '/', geolocations.id))"         }       ]     },     "JIT": {       "Worker Number": -1,       "Functions": 8,       "Options": {         "Inlining": true,         "Optimization": true,         "Expressions": true,         "Deforming": true       }     }   } ]

While combining them with a union takes 1 sec 916 ms

SELECT * FROM geolocations WHERE EXISTS (    SELECT 1 FROM geolocations g2    WHERE g2.ancestry =        CONCAT(geolocations.ancestry, '/', geolocations.id) ) UNION SELECT * FROM geolocations WHERE location_type = 2

Explain

[   {     "Plan": {       "Node Type": "Unique",       "Parallel Aware": false,       "Startup Cost": 308693.44,       "Total Cost": 332506.74,       "Plan Rows": 865938,       "Plan Width": 188,       "Plans": [         {           "Node Type": "Sort",           "Parent Relationship": "Outer",           "Parallel Aware": false,           "Startup Cost": 308693.44,           "Total Cost": 310858.29,           "Plan Rows": 865938,           "Plan Width": 188,           "Sort Key": [             "geolocations.id",             "geolocations.name",             "geolocations.location_type",             "geolocations.pricing",             "geolocations.ancestry",             "geolocations.geolocationable_id",             "geolocations.geolocationable_type",             "geolocations.created_at",             "geolocations.updated_at",             "geolocations.info"           ],           "Plans": [             {               "Node Type": "Append",               "Parent Relationship": "Outer",               "Parallel Aware": false,               "Startup Cost": 15851.41,               "Total Cost": 63464.05,               "Plan Rows": 865938,               "Plan Width": 188,               "Subplans Removed": 0,               "Plans": [                 {                   "Node Type": "Hash Join",                   "Parent Relationship": "Member",                   "Parallel Aware": false,                   "Join Type": "Inner",                   "Startup Cost": 15851.41,                   "Total Cost": 35074.94,                   "Plan Rows": 299882,                   "Plan Width": 68,                   "Inner Unique": true,                   "Hash Cond": "(concat(geolocations.ancestry, '/', geolocations.id) = (g2.ancestry)::text)",                   "Plans": [                     {                       "Node Type": "Seq Scan",                       "Parent Relationship": "Outer",                       "Parallel Aware": false,                       "Relation Name": "geolocations",                       "Alias": "geolocations",                       "Startup Cost": 0,                       "Total Cost": 13900.63,                       "Plan Rows": 599763,                       "Plan Width": 68                     },                     {                       "Node Type": "Hash",                       "Parent Relationship": "Inner",                       "Parallel Aware": false,                       "Startup Cost": 15600.65,                       "Total Cost": 15600.65,                       "Plan Rows": 20061,                       "Plan Width": 12,                       "Plans": [                         {                           "Node Type": "Aggregate",                           "Strategy": "Hashed",                           "Partial Mode": "Simple",                           "Parent Relationship": "Outer",                           "Parallel Aware": false,                           "Startup Cost": 15400.04,                           "Total Cost": 15600.65,                           "Plan Rows": 20061,                           "Plan Width": 12,                           "Group Key": [                             "(g2.ancestry)::text"                           ],                           "Plans": [                             {                               "Node Type": "Seq Scan",                               "Parent Relationship": "Outer",                               "Parallel Aware": false,                               "Relation Name": "geolocations",                               "Alias": "g2",                               "Startup Cost": 0,                               "Total Cost": 13900.63,                               "Plan Rows": 599763,                               "Plan Width": 12                             }                           ]                         }                       ]                     }                   ]                 },                 {                   "Node Type": "Seq Scan",                   "Parent Relationship": "Member",                   "Parallel Aware": false,                   "Relation Name": "geolocations",                   "Alias": "geolocations_1",                   "Startup Cost": 0,                   "Total Cost": 15400.04,                   "Plan Rows": 566056,                   "Plan Width": 68,                   "Filter": "(location_type = 2)"                 }               ]             }           ]         }       ]     },     "JIT": {       "Worker Number": -1,       "Functions": 15,       "Options": {         "Inlining": false,         "Optimization": false,         "Expressions": true,         "Deforming": true       }     }   } ]

My question is, why does postgresql execute the OR query much slower?

Performance of select from a 3d list – Mathematica slower than Python

I am creating a random 3d data set in Matematica 12.1. Then I am selecting all points that are in a certain range of one axis.

The same I am doing in Python (same computer, Python 3.8.5, numpy 1.19.2)

RESULT: It seems that Python is able to select much faster (1.7 sec) than Mathematica (5.2 sec). What is the reason for that? For selection in Mathematica I used the fastest solution, which is by Carl Woll (see here at bottom).

SeedRandom[1]; coordinates = RandomReal[10, {100000000, 3}];  selectedCoordinates =     Pick[coordinates,      Unitize@Clip[coordinates[[All, 1]], {6, 7}, {0, 0}],      1]; // AbsoluteTiming  {5.16326, Null}  Dimensions[coordinates]  {100000000, 3}  Dimensions[selectedCoordinates]  {10003201, 3}

PYTHON CODE:

import time import numpy as np   np.random.seed(1) coordinates = np.random.random_sample((100000000,3))*10  start = time.time() selectedCoordinates = coordinates[(coordinates[:,0] > 6) & (coordinates[:,0] < 7)] end = time.time()  print(end-start)  print(coordinates.shape)  print(selectedCoordinates.shape)  1.6979997158050537  (100000000, 3)  (9997954, 3)

Using double write buffer is 8x slower in SSD (compared with 2x~3x in HDD)

I understand the double-write-buffer enhances the reliability of data, so it makes transactions slower. But it is amazing that the slow down is such severe in the newest Samsung 980 pro (M.2 PCIe 4.0, which is about 400$ for 1TB).

Workload: https://github.com/Percona-Lab/tpcc-mysql
Configurations: other parameters are defaults.
CPU: AMD Ryzen 3900XT
MEM: 64GB, 3200MHz
OS: Ubuntu 20.10, all disks are ext4
MySQL: 8.0.22

Why does this happen? Did I hit a performance bug?

Thanks!

en ter image description here

Cluster is causing my query to run SLOWER then before

So I have been stuck on this for 6 hours now and I have no clue what to do. I am doing university homework that requires us to create a unoptomized sql query (does not have to make sense) and then apply index’s and see if it makes it faster (which it did for me, from 0.70 elapsed time to 0.66) and then we had to apply clusters.

I applied clusters and it has now almost doubled the amount taken to finish the query. From 0.70 to 1.15. Below is how I specified my cluster:

CREATE CLUSTER customer2_custid25 (custid NUMBER(8))    SIZE 270  TABLESPACE student_ts;

I tried all my previous times with INITIAL and NEXT but that seemed not to make a difference. Below are the tables:

CREATE TABLE CUSTOMER18 (       CustID         NUMBER(8) NOT NULL,     FIRST_NAME     VARCHAR2(15),     SURNAME     VARCHAR2(15),     ADDRESS     VARCHAR2(20),     PHONE_NUMBER NUMBER(12))       CLUSTER customer2_custid25(CustID);   CREATE TABLE product18(       ProdID     NUMBER(10) NOT NULL,     PName    Varchar2(6),     PDesc    Varchar2(15),     Price    Number(8),     QOH        Number(5));   CREATE TABLE sales18(       SaleID     NUMBER(10) NOT NULL,     SaleDate    DATE,     Qty            Number(5),     SellPrice    Number(10),     CustID        NUMBER(8),     ProdID        NUMBER(10))       CLUSTER customer2_custid25(CustID);     CREATE INDEX customer2_custid_clusterindxqg ON CLUSTER customer2_custid25 TABLESPACE student_ts ;

I also tried taking the tablespace section in the cluster index away.

I followed this formula to help calculate cluster sizes:

"Size of a cluster is the size of a parent row + (size of Child row * average number of children). "

This brought me to the size of 270. However, after testing sizes (going up 20) from 250 to 350 I found 320 to be the fastest at 1.15.

No matter what I try, I can not for the love of me get it lower then my base query times.

Other students have done the same and halved their query time.

All help is really appreciated.

Ray-Box (AABB) is slower than without

at the moment im trying my best to write my own raytracer (and its quite fun actually). So the last days I tried implementing a bounding box algorithm to it. But im getting a much slower framerate with the bounding boxes turned on :(. I think it has something to do with checking the box with every ray but I dont know how I could change that. Here is my code:

the Intersection algorythm

bool Intersect(Ray r, float3 lb, float3 rt) {     float3 dir_inv = 1 / r.direction;          double t1 = (lb[0] - r.origin[0]) * dir_inv[0];     double t2 = (rt[0] - r.origin[0]) * dir_inv[0];      double tmin = min(t1, t2);     double tmax = max(t1, t2);      for (int i = 1; i < 3; ++i)     {         t1 = (lb[i] - r.origin[i]) * dir_inv[i];         t2 = (rt[i] - r.origin[i]) * dir_inv[i];          tmin = max(tmin, min(t1, t2));         tmax = min(tmax, max(t1, t2));     }      return tmax > max(tmin, 0.0); }

My trace function:

RayHit Trace(Ray ray) {     RayHit bestHit = CreateRayHit();     uint count, stride, i;      // Trace ground plane     IntersectGroundPlane(ray, bestHit);      // Trace spheres     _Spheres.GetDimensions(count, stride);     for (i = 0; i < count; i++)     {         if (Intersect(ray, _Spheres[i].position - (_Spheres[i].radius), _Spheres[i].position + (_Spheres[i].radius)))             IntersectSphere(ray, bestHit, _Spheres[i]);     }          // Trace mesh objects     _MeshObjects.GetDimensions(count, stride);     for (i = 0; i < count; i++)     {         //if (Intersect(ray, float3(0.0f, 0.0f, 0.0f), float3(10.0f, 10.0f, 10.0f)))             //IntersectMeshObject(ray, bestHit, _MeshObjects[i]);     }      return bestHit; }

thanks in advance

Radix sort slower than Quick sort?

I would like to demonstrate that sometime radix-sort is better than quick-sort. In this example I am using the program below: 

#include <stdio.h> #include <stdlib.h> #include <assert.h> #include <string.h> #include <time.h> #include <math.h>  int cmpfunc (const void * a, const void * b) {    return ( *(int*)a - *(int*)b ); }  void bin_radix_sort(int *a, const long long size, int digits) {     assert(digits % 2 == 0);      long long count[2];     int *b = malloc(size * sizeof(int));     int exp = 0;      while(digits--) {         // Count elements         count[0] = count[1] = 0;         for (int i = 0; i < size; i++)             count[(a[i] >> exp) & 0x01]++;          // Cumulative sum         count[1] += count[0];          // Build output array         for (int i = size - 1; i >= 0; i--)             b[--count[(a[i] >> exp) & 0x01]] = a[i];          exp++;         int *p = a; a = b; b = p;     };      free(b); }  struct timespec start;  void tic() {     timespec_get(&start, TIME_UTC); }  double toc() {     struct timespec stop;     timespec_get(&stop, TIME_UTC);     return stop.tv_sec - start.tv_sec + (         stop.tv_nsec - start.tv_nsec     ) * 1e-9; }  int main(void) {     const long long n = 1024 * 1024 * 50;     printf("Init memory (%lld MB)...\n", n / 1024 / 1024 * sizeof(int));      int *data = calloc(n, sizeof(int));      printf("Sorting n = %lld data elements...\n", n);      long long O;     tic();     O = n * log(n);     qsort(data, n, sizeof(data[0]), cmpfunc);     printf("%lld %lf s\n", O, toc());      int d = 6;     tic();     O = d * (n + 2);     bin_radix_sort(data, n, d);     printf("%lld %lf s\n", O, toc()); }

It performs as follow:

$   gcc bench.c -lm $   ./a.out  Init memory (200 MB)... Sorting n = 52428800 data elements... 931920169 1.858300 s 314572812 1.541998 s

I know that Quick Sort will perform in O(n log n) while Radix Sort will be in O(d (n + r)) ~= O(6 * n). For n = 52428800, log(n) = 17. I am then expecting Radix Sort to be 3 times faster than Quick Sort…

This is not what I observe.

What am I missing?

Aurora PostgreSQL database using a slower query plan than a normal PostgreSQL for an identical query?

Following the migration of an application and its database from a classical PostgreSQL database to an Amazon Aurora RDS PostgreSQL database (both using 9.6 version), we have found that a specific query is running much slower — around 10 times slower — on Aurora than on PostgreSQL.

Both databases have the same configuration, be it for the hardware or the pg_conf.

The query itself is fairly simple. It is generated from our backend written in Java and using jOOQ for writing the queries: 

with "all_acp_ids"("acp_id") as (     select acp_id from temp_table_de3398bacb6c4e8ca8b37be227eac089 )  select distinct "public"."f1_folio_milestones"."acp_id",      coalesce("public"."sa_milestone_overrides"."team",      "public"."f1_folio_milestones"."team_responsible")  from "public"."f1_folio_milestones"  left outer join      "public"."sa_milestone_overrides" on (         "public"."f1_folio_milestones"."milestone" = "public"."sa_milestone_overrides"."milestone"          and "public"."f1_folio_milestones"."view" = "public"."sa_milestone_overrides"."view"          and "public"."f1_folio_milestones"."acp_id" = "public"."sa_milestone_overrides"."acp_id" ) where "public"."f1_folio_milestones"."acp_id" in (     select "all_acp_ids"."acp_id" from "all_acp_ids" )

With temp_table_de3398bacb6c4e8ca8b37be227eac089 being a single-column table, f1_folio_milestones (17 million entries) and sa_milestone_overrides (Around 1 million entries) being similarly designed tables having indexes on all the columns used for the LEFT OUTER JOIN.

When we run it on the normal PostgreSQL database, it generates the following query plan:

Unique  (cost=4802622.20..4868822.51 rows=8826708 width=43) (actual time=483.928..483.930 rows=1 loops=1)   CTE all_acp_ids     ->  Seq Scan on temp_table_de3398bacb6c4e8ca8b37be227eac089  (cost=0.00..23.60 rows=1360 width=32) (actual time=0.004..0.005 rows=1 loops=1)   ->  Sort  (cost=4802598.60..4824665.37 rows=8826708 width=43) (actual time=483.927..483.927 rows=4 loops=1)         Sort Key: f1_folio_milestones.acp_id, (COALESCE(sa_milestone_overrides.team, f1_folio_milestones.team_responsible))         Sort Method: quicksort  Memory: 25kB         ->  Hash Left Join  (cost=46051.06..3590338.34 rows=8826708 width=43) (actual time=483.905..483.917 rows=4 loops=1)               Hash Cond: ((f1_folio_milestones.milestone = sa_milestone_overrides.milestone) AND (f1_folio_milestones.view = (sa_milestone_overrides.view)::text) AND (f1_folio_milestones.acp_id = (sa_milestone_overrides.acp_id)::text))               ->  Nested Loop  (cost=31.16..2572.60 rows=8826708 width=37) (actual time=0.029..0.038 rows=4 loops=1)                     ->  HashAggregate  (cost=30.60..32.60 rows=200 width=32) (actual time=0.009..0.010 rows=1 loops=1)                           Group Key: all_acp_ids.acp_id                           ->  CTE Scan on all_acp_ids  (cost=0.00..27.20 rows=1360 width=32) (actual time=0.006..0.007 rows=1 loops=1)                     ->  Index Scan using f1_folio_milestones_acp_id_idx on f1_folio_milestones  (cost=0.56..12.65 rows=5 width=37) (actual time=0.018..0.025 rows=4 loops=1)                           Index Cond: (acp_id = all_acp_ids.acp_id)               ->  Hash  (cost=28726.78..28726.78 rows=988178 width=34) (actual time=480.423..480.423 rows=987355 loops=1)                     Buckets: 1048576  Batches: 1  Memory Usage: 72580kB                     ->  Seq Scan on sa_milestone_overrides  (cost=0.00..28726.78 rows=988178 width=34) (actual time=0.004..189.641 rows=987355 loops=1) Planning time: 3.561 ms Execution time: 489.223 ms

And it goes pretty smoothly as one can see — less than a second for the query. But on the Aurora instance, this happens:

Unique  (cost=2632927.29..2699194.83 rows=8835672 width=43) (actual time=4577.348..4577.350 rows=1 loops=1)   CTE all_acp_ids     ->  Seq Scan on temp_table_de3398bacb6c4e8ca8b37be227eac089  (cost=0.00..23.60 rows=1360 width=32) (actual time=0.001..0.001 rows=1 loops=1)   ->  Sort  (cost=2632903.69..2654992.87 rows=8835672 width=43) (actual time=4577.348..4577.348 rows=4 loops=1)         Sort Key: f1_folio_milestones.acp_id, (COALESCE(sa_milestone_overrides.team, f1_folio_milestones.team_responsible))         Sort Method: quicksort  Memory: 25kB         ->  Merge Left Join  (cost=1321097.58..1419347.08 rows=8835672 width=43) (actual time=4488.369..4577.330 rows=4 loops=1)               Merge Cond: ((f1_folio_milestones.view = (sa_milestone_overrides.view)::text) AND (f1_folio_milestones.milestone = sa_milestone_overrides.milestone) AND (f1_folio_milestones.acp_id = (sa_milestone_overrides.acp_id)::text))               ->  Sort  (cost=1194151.06..1216240.24 rows=8835672 width=37) (actual time=0.039..0.040 rows=4 loops=1)                     Sort Key: f1_folio_milestones.view, f1_folio_milestones.milestone, f1_folio_milestones.acp_id                     Sort Method: quicksort  Memory: 25kB                     ->  Nested Loop  (cost=31.16..2166.95 rows=8835672 width=37) (actual time=0.022..0.028 rows=4 loops=1)                           ->  HashAggregate  (cost=30.60..32.60 rows=200 width=32) (actual time=0.006..0.006 rows=1 loops=1)                                 Group Key: all_acp_ids.acp_id                                 ->  CTE Scan on all_acp_ids  (cost=0.00..27.20 rows=1360 width=32) (actual time=0.003..0.004 rows=1 loops=1)                           ->  Index Scan using f1_folio_milestones_acp_id_idx on f1_folio_milestones  (cost=0.56..10.63 rows=4 width=37) (actual time=0.011..0.015 rows=4 loops=1)                                 Index Cond: (acp_id = all_acp_ids.acp_id)               ->  Sort  (cost=126946.52..129413.75 rows=986892 width=34) (actual time=4462.727..4526.822 rows=448136 loops=1)                     Sort Key: sa_milestone_overrides.view, sa_milestone_overrides.milestone, sa_milestone_overrides.acp_id                     Sort Method: quicksort  Memory: 106092kB                     ->  Seq Scan on sa_milestone_overrides  (cost=0.00..28688.92 rows=986892 width=34) (actual time=0.003..164.348 rows=986867 loops=1) Planning time: 1.394 ms Execution time: 4583.295 ms

It effectively has a lower global cost, but takes almost 10 times as much time than before!

Disabling merge joins makes Aurora revert to a hash join, which gives the expected execution time — but permanently disabling it is not an option. Curiously though, disabling nested loops gives an even better result while still using a merge join…

Unique  (cost=3610230.74..3676431.05 rows=8826708 width=43) (actual time=2.465..2.466 rows=1 loops=1)   CTE all_acp_ids     ->  Seq Scan on temp_table_de3398bacb6c4e8ca8b37be227eac089  (cost=0.00..23.60 rows=1360 width=32) (actual time=0.004..0.004 rows=1 loops=1)   ->  Sort  (cost=3610207.14..3632273.91 rows=8826708 width=43) (actual time=2.464..2.464 rows=4 loops=1)         Sort Key: f1_folio_milestones.acp_id, (COALESCE(sa_milestone_overrides.team, f1_folio_milestones.team_responsible))         Sort Method: quicksort  Memory: 25kB         ->  Merge Left Join  (cost=59.48..2397946.87 rows=8826708 width=43) (actual time=2.450..2.455 rows=4 loops=1)               Merge Cond: (f1_folio_milestones.acp_id = (sa_milestone_overrides.acp_id)::text)               Join Filter: ((f1_folio_milestones.milestone = sa_milestone_overrides.milestone) AND (f1_folio_milestones.view = (sa_milestone_overrides.view)::text))               ->  Merge Join  (cost=40.81..2267461.88 rows=8826708 width=37) (actual time=2.312..2.317 rows=4 loops=1)                     Merge Cond: (f1_folio_milestones.acp_id = all_acp_ids.acp_id)                     ->  Index Scan using f1_folio_milestones_acp_id_idx on f1_folio_milestones  (cost=0.56..2223273.29 rows=17653416 width=37) (actual time=0.020..2.020 rows=1952 loops=1)                     ->  Sort  (cost=40.24..40.74 rows=200 width=32) (actual time=0.011..0.012 rows=1 loops=1)                           Sort Key: all_acp_ids.acp_id                           Sort Method: quicksort  Memory: 25kB                           ->  HashAggregate  (cost=30.60..32.60 rows=200 width=32) (actual time=0.008..0.008 rows=1 loops=1)                                 Group Key: all_acp_ids.acp_id                                 ->  CTE Scan on all_acp_ids  (cost=0.00..27.20 rows=1360 width=32) (actual time=0.005..0.005 rows=1 loops=1)               ->  Materialize  (cost=0.42..62167.38 rows=987968 width=34) (actual time=0.021..0.101 rows=199 loops=1)                     ->  Index Scan using sa_milestone_overrides_acp_id_index on sa_milestone_overrides  (cost=0.42..59697.46 rows=987968 width=34) (actual time=0.019..0.078 rows=199 loops=1) Planning time: 5.500 ms Execution time: 2.516 ms

We have asked the AWS support team, they are still looking at the issue, but we are wondering what could cause that issue to happen. What could explain such a behaviour difference?

While looking at some of the documentation for the database, I read that Aurora favors cost over time — and hence it uses the query plan that has the lowest cost.

But as we can see, it’s far from being optimal given its response time… Is there a threshold or a setting that could make the database use a more expensive — but faster — query plan?

Would Triple DES-X with 9 keys be much slower than standard Triple DES?

Since a single hardware pass of an XOR with a 64 bit key is very fast, would Triple DES-X using nine 64 bit keys used in the following manner be virtually identical in terms of code size, memory consumption, and execution speed to 3DES?

XOR (Key 1) DES (Key 2) XOR (Key 3)

XOR (Key 4) DES (Key 5) XOR (Key 6)

XOR (Key 7) DES (Key 8) XOR (Key 9)

Additionally, would it be significantly stronger? Would it still suffer from the same block size based vulnerability of DES-X?