How to limit PostgreSQL INSERTs to assure that the caller has an attribute conistent with the parent table?

In the parent table, there is a column that defines the ‘owner’ of the row. When inserting into the child, the caller provides an owner id or ‘%’, to indicate that the the caller is the administrator. I was expecting the insert with this check to be slower that a straight insert, but I didn’t expect a 70x penalty. Can you give me some ideas for how to optimize the performance to achieve the same result as this statement?

INSERT INTO child (parent_key, value1, value2)   SELECT $  1, $  2, $  3 FROM parent      WHERE parent_key = $  1       AND owner LIKE $  4     LIMIT 1; 

Table definitions:

CREATE TABLE parent (   parent_key VARCHAR(255) PRIMARY KEY,    owner VARCHAR(255) );  CREATE TABLE child (   child_key SERIAL PRIMARY KEY,    parent_key VARCHAR(255) REFERENCES parent,    value1 VARCHAR(255),    value2 VARCHAR(255) ); 

I ran an explain on my statement, and this is what I see.

 Insert on child  (cost=0.42..8.46 rows=1 width=1670)    ->  Subquery Scan on "*SELECT*"  (cost=0.42..8.46 rows=1 width=1670)          ->  Limit  (cost=0.42..8.44 rows=1 width=296)                ->  Index Scan using parent_pkey on parent  (cost=0.42..8.44 rows=1 width=296)                      Index Cond: ((parent_key)::text = '111'::text)                      Filter: ((owner)::text ~~ '%'::text) 

Since parent_pkey is a unique index, I would expect the LIKE filter to contribute an insignificant amount to the execution time. This conditional INSERT takes >70 times as long as an INSERT of VALUES. What would be a more efficient way of enforcing this constraint?

Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint ’employee_ibfk_1′ in the referenced table ‘branch’

strong text I AM GETTING THE ABOVE ERROR AFTER EXECUTING ALTER TABLE STATEMENT… I HAVE ASSIGNED THE SAME DATA TYPES for both ids’s. HOW TO RESOLVE THE ABOVE ERROR.

CREATE TABLE Employee( Emp_ID INT PRIMARY KEY, First_Name VARCHAR(25), Last_Name VARCHAR(25), Birth_Day DATE, Sex VARCHAR(1), Salary INT, Super_ID INT, Branch_ID INT );

CREATE TABLE Branch( Branch_ID INT, Branch_Name VARCHAR(40), Mgr_ID INT, Mgr_Start_Date DATE, FOREIGN KEY(Mgr_ID) REFERENCES Employee(Emp_ID) );

ALTER TABLE Employee ADD FOREIGN KEY(Branch_ID) REFERENCES Branch(Branch_ID) ON DELETE SET NULL;

Website with interactive table (and charts)

Hi,
I am totally new web developing but I hope I can find some advise here where to look further.

I've got an idea I am very eager to realize.
Basically, the idea is that I want to compile certain mutual funds into a table (listing fees, past performance, Morningstar-rating etc) and then make the user sorts/filter by criteria of their choice and multi-select funds, to build their portfolio. Based on their selection, I want charts to be created (e.g. showing the distribution of e.g….

Website with interactive table (and charts)

Non-deterministic performance of query on select, from 1s to 60s on table with 1 billion rows

I’m trying to investigate why the performance of this query is so non-deterministic. It can take anywhere from 1 seconds, to 60 seconds and above. The nature of the query is to select a "time window", and get all rows from within that time window.

Here’s the query in question, running on a table of approximately 1 billion rows:

SELECT CAST(extract(EPOCH from ts)*1000000 as bigint) as ts     , ticks     , quantity     , side FROM order_book WHERE ts >= TO_TIMESTAMP(1618882633073383/1000000.0)     AND ts < TO_TIMESTAMP(1618969033073383/1000000.0)     AND zx_prod_id = 0 ORDER BY ts ASC, del desc 

The values within TO_TIMESTAMP will keep sliding forward as I walk the whole table. Here is the EXPLAIN ANALYZE output for the same query on two different time windows:

Slow Performance

Gather Merge  (cost=105996.20..177498.48 rows=586308 width=18) (actual time=45196.559..45280.769 rows=539265 loops=1)   Workers Planned: 6   Workers Launched: 6   Buffers: shared hit=116386 read=42298   ->  Sort  (cost=104996.11..105240.40 rows=97718 width=18) (actual time=45169.717..45176.775 rows=77038 loops=7)         Sort Key: (((date_part('epoch'::text, _hyper_16_214_chunk.ts) * '1000000'::double precision))::bigint), _hyper_16_214_chunk.del DESC         Sort Method: quicksort  Memory: 9327kB         Worker 0:  Sort Method: quicksort  Memory: 8967kB         Worker 1:  Sort Method: quicksort  Memory: 9121kB         Worker 2:  Sort Method: quicksort  Memory: 9098kB         Worker 3:  Sort Method: quicksort  Memory: 9075kB         Worker 4:  Sort Method: quicksort  Memory: 9019kB         Worker 5:  Sort Method: quicksort  Memory: 9031kB         Buffers: shared hit=116386 read=42298         ->  Result  (cost=0.57..96897.07 rows=97718 width=18) (actual time=7.475..45131.932 rows=77038 loops=7)               Buffers: shared hit=116296 read=42298               ->  Parallel Index Scan using _hyper_16_214_chunk_order_book_ts_idx on _hyper_16_214_chunk  (cost=0.57..95187.01 rows=97718 width=18) (actual time=7.455..45101.670 rows=77038 loops=7)                     Index Cond: ((ts >= '2021-04-22 01:34:31.357179+00'::timestamp with time zone) AND (ts < '2021-04-22 02:34:31.357179+00'::timestamp with time zone))                     Filter: (zx_prod_id = 0)                     Rows Removed by Filter: 465513                     Buffers: shared hit=116296 read=42298 Planning Time: 1.107 ms JIT:   Functions: 49   Options: Inlining false, Optimization false, Expressions true, Deforming true   Timing: Generation 9.273 ms, Inlining 0.000 ms, Optimization 2.008 ms, Emission 36.235 ms, Total 47.517 ms Execution Time: 45335.178 ms 

Fast Performance

Gather Merge  (cost=105095.94..170457.62 rows=535956 width=18) (actual time=172.723..240.628 rows=546367 loops=1)   Workers Planned: 6   Workers Launched: 6   Buffers: shared hit=158212   ->  Sort  (cost=104095.84..104319.16 rows=89326 width=18) (actual time=146.702..152.849 rows=78052 loops=7)         Sort Key: (((date_part('epoch'::text, _hyper_16_214_chunk.ts) * '1000000'::double precision))::bigint), _hyper_16_214_chunk.del DESC         Sort Method: quicksort  Memory: 11366kB         Worker 0:  Sort Method: quicksort  Memory: 8664kB         Worker 1:  Sort Method: quicksort  Memory: 8986kB         Worker 2:  Sort Method: quicksort  Memory: 9116kB         Worker 3:  Sort Method: quicksort  Memory: 8858kB         Worker 4:  Sort Method: quicksort  Memory: 9057kB         Worker 5:  Sort Method: quicksort  Memory: 6611kB         Buffers: shared hit=158212         ->  Result  (cost=0.57..96750.21 rows=89326 width=18) (actual time=6.145..127.591 rows=78052 loops=7)               Buffers: shared hit=158122               ->  Parallel Index Scan using _hyper_16_214_chunk_order_book_ts_idx on _hyper_16_214_chunk  (cost=0.57..95187.01 rows=89326 width=18) (actual time=6.124..114.023 rows=78052 loops=7)                     Index Cond: ((ts >= '2021-04-22 01:34:31.357179+00'::timestamp with time zone) AND (ts < '2021-04-22 02:34:31.357179+00'::timestamp with time zone))                     Filter: (zx_prod_id = 4)                     Rows Removed by Filter: 464498                     Buffers: shared hit=158122 Planning Time: 0.419 ms JIT:   Functions: 49   Options: Inlining false, Optimization false, Expressions true, Deforming true   Timing: Generation 10.405 ms, Inlining 0.000 ms, Optimization 2.185 ms, Emission 39.188 ms, Total 51.778 ms Execution Time: 274.413 ms 

I interpreted this output as most of the blame lying on this parallel index scan.

At first, I tried to raise work_mem to 1 GB and shared_buffers to 24 GB, thinking that maybe it couldn’t fit all the stuff it needed in RAM, but that didn’t seem to help.

Next, I tried creating an index on (zx_prod_id, ts), thinking that the filter on the parallel index scan might be taking a while, but that didn’t seem to do anything either.

I’m no database expert, and so I’ve kind of exhausted the limits of my knowledge.

Thanks in advance for any suggestions!

How can I create a temp table from a SELECT in Azure Synapse

I have the following statement that works when I omit the ORDER BY Clause or if I run it as a select and omit the create table part but I need both to ensure my generated key is sequenced correctly

Any ideas?

Msg 104381, Level 16, State 1, Line 18 The ORDER BY clause is invalid in views, CREATE TABLE AS SELECT, INSERT SELECT, SELECT INTO, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

CREATE TABLE #demo WITH (DISTRIBUTION = ROUND_ROBIN) AS SELECT         ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS ID,        schemas.name as [schema],         tables.name as [table],         columns.column_id as [ordinal],        columns.name as [column],        types.name as [type] FROM SYS.COLUMNS   inner join sys.types           on  types.system_type_id = columns.system_type_id   inner join sys.tables           on  tables.object_id = columns.object_id   inner join sys.schemas           on  schemas.schema_id = tables.schema_id order by schemas.name,         tables.name,         columns.column_id  

PostgreSQL 13 – Improve huge table data aggregation

I have a huge database (current size is ~900GB and new data still comes) partitioned by Year_month and subpartition by currency. The problem is when I try to fetch aggregation from the whole partition it goes slow. This is a report so it will be queried very often. The current size of partition which I want to aggregate: 7.829.230 rows. Each subpartition will be similar. Table schema (anonymized):

-- auto-generated definition CREATE TABLE aggregates_dates (     currency              CHAR(3)                                    NOT NULL,     id                    uuid            DEFAULT uuid_generate_v4() NOT NULL,     date                  TIMESTAMP(0)                               NOT NULL,     currency              CHAR(3)                                    NOT NULL,     field01               INTEGER                                    NOT NULL,     field02               INTEGER                                    NOT NULL,     field03               INTEGER                                    NOT NULL,     field04               INTEGER                                    NOT NULL,     field05               INTEGER                                    NOT NULL,     field06               CHAR(2)                                    NOT NULL,     field07               INTEGER         DEFAULT 0                  NOT NULL,     field08               INTEGER         DEFAULT 0                  NOT NULL,     field09               INTEGER         DEFAULT 0                  NOT NULL,     field10               INTEGER         DEFAULT 0                  NOT NULL,     field11               INTEGER         DEFAULT 0                  NOT NULL,     value01               INTEGER         DEFAULT 0                  NOT NULL,     value02               INTEGER         DEFAULT 0                  NOT NULL,     value03               INTEGER         DEFAULT 0                  NOT NULL,     value04               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value05               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value06               INTEGER         DEFAULT 0                  NOT NULL,     value07               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value08               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value09               INTEGER         DEFAULT 0                  NOT NULL,     value10               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value11               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value12               INTEGER         DEFAULT 0                  NOT NULL,     value13               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value14               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value15               INTEGER         DEFAULT 0                  NOT NULL,     value16               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value17               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value18               NUMERIC(24, 12) DEFAULT '0'::NUMERIC       NOT NULL,     value19               INTEGER         DEFAULT 0,     value20               INTEGER         DEFAULT 0,     CONSTRAINT aggregates_dates_pkey         PRIMARY KEY (id, date, currency) )     PARTITION BY RANGE (date); CREATE TABLE aggregates_dates_2020_01     PARTITION OF aggregates_dates         (             CONSTRAINT aggregates_dates_2020_01_pkey                 PRIMARY KEY (id, date, currency)             )         FOR VALUES FROM ('2020-01-01 00:00:00') TO ('2020-01-31 23:59:59')     PARTITION BY LIST (currency); CREATE TABLE aggregates_dates_2020_01_eur     PARTITION OF aggregates_dates_2020_01         (             CONSTRAINT aggregates_dates_2020_01_eur_pkey                 PRIMARY KEY (id, date, currency)             )         FOR VALUES IN ('EUR'); CREATE INDEX aggregates_dates_2020_01_eur_date_idx ON aggregates_dates_2020_01_eur (date); CREATE INDEX aggregates_dates_2020_01_eur_field01_idx ON aggregates_dates_2020_01_eur (field01); CREATE INDEX aggregates_dates_2020_01_eur_field02_idx ON aggregates_dates_2020_01_eur (field02); CREATE INDEX aggregates_dates_2020_01_eur_field03_idx ON aggregates_dates_2020_01_eur (field03); CREATE INDEX aggregates_dates_2020_01_eur_field04_idx ON aggregates_dates_2020_01_eur (field04); CREATE INDEX aggregates_dates_2020_01_eur_field06_idx ON aggregates_dates_2020_01_eur (field06); CREATE INDEX aggregates_dates_2020_01_eur_currency_idx ON aggregates_dates_2020_01_eur (currency); CREATE INDEX aggregates_dates_2020_01_eur_field09_idx ON aggregates_dates_2020_01_eur (field09); CREATE INDEX aggregates_dates_2020_01_eur_field10_idx ON aggregates_dates_2020_01_eur (field10); CREATE INDEX aggregates_dates_2020_01_eur_field11_idx ON aggregates_dates_2020_01_eur (field11); CREATE INDEX aggregates_dates_2020_01_eur_field05_idx ON aggregates_dates_2020_01_eur (field05); CREATE INDEX aggregates_dates_2020_01_eur_field07_idx ON aggregates_dates_2020_01_eur (field07); CREATE INDEX aggregates_dates_2020_01_eur_field08_idx ON aggregates_dates_2020_01_eur (field08); 

Example Query (not all fields used) which aggregate whole partition (This query might have many more WHERE conditions but this one is the worst case)

EXPLAIN (ANALYSE, BUFFERS, VERBOSE) SELECT        COALESCE(SUM(mainTable.value01), 0)            AS                                    "value01",        COALESCE(SUM(mainTable.value02), 0)       AS                                    "value02",        COALESCE(SUM(mainTable.value03), 0)       AS                                    "value03",        COALESCE(SUM(mainTable.value06), 0)       AS                                    "value06",        COALESCE(SUM(mainTable.value09), 0)    AS                                    "value09",        COALESCE(SUM(mainTable.value12), 0)      AS                                    "value12",        COALESCE(SUM(mainTable.value15), 0) AS                                    "value15",        COALESCE(SUM(mainTable.value03 + mainTable.value06 + mainTable.value09 + mainTable.value12 +                     mainTable.value15), 0) AS                                    "kpi01",        COALESCE(SUM(mainTable.value05) * 1, 0)                                         "value05",        COALESCE(SUM(mainTable.value08) * 1, 0)                                         "value08",        COALESCE(SUM(mainTable.value11) * 1, 0)                                      "value11",        COALESCE(SUM(mainTable.value14) * 1, 0)                                        "value14",        COALESCE(SUM(mainTable.value17) * 1, 0)                                   "value17",        COALESCE(SUM(mainTable.value05 + mainTable.value08 + mainTable.value11 + mainTable.value14 +                     mainTable.value17) * 1, 0)                                   "kpi02",        CASE            WHEN SUM(mainTable.value02) > 0 THEN (1.0 * SUM(                        mainTable.value05 + mainTable.value08 + mainTable.value11 +                        mainTable.value14 + mainTable.value17) / SUM(mainTable.value02) * 1000 * 1)            ELSE 0 END                                                                      "kpiEpm",        CASE            WHEN SUM(mainTable.value01) > 0 THEN (1.0 * SUM(                        mainTable.value05 + mainTable.value08 + mainTable.value11 +                        mainTable.value14) / SUM(mainTable.value01) * 1)            ELSE 0 END FROM performance mainTable WHERE (mainTable.date BETWEEN '2020-01-01 00:00:00' AND '2020-02-01 00:00:00')   AND (mainTable.currency = 'EUR') GROUP BY mainTable.field02; 

EXPLAIN:

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN                                                                                                                                                                          | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |HashAggregate  (cost=3748444.51..3748502.07 rows=794 width=324) (actual time=10339.771..10340.497 rows=438 loops=1)                                                                 | |  Group Key: maintable.field02                                                                                                                                                      | |  Batches: 1  Memory Usage: 1065kB                                                                                                                                                  | |  Buffers: shared hit=2445343                                                                                                                                                       | |  ->  Append  (cost=0.00..2706608.65 rows=11575954 width=47) (actual time=212.934..4549.921 rows=7829230 loops=1)                                                                   | |        Buffers: shared hit=2445343                                                                                                                                                 | |        ->  Seq Scan on performance_2020_01 maintable_1  (cost=0.00..2646928.38 rows=11570479 width=47) (actual time=212.933..4055.104 rows=7823923 loops=1)                        | |              Filter: ((date >= '2020-01-01 00:00:00'::timestamp without time zone) AND (date <= '2020-02-01 00:00:00'::timestamp without time zone) AND (currency = 'EUR'::bpchar))| |              Buffers: shared hit=2444445                                                                                                                                           | |        ->  Index Scan using performance_2020_02_date_idx on performance_2020_02 maintable_2  (cost=0.56..1800.50 rows=5475 width=47) (actual time=0.036..6.476 rows=5307 loops=1)  | |              Index Cond: ((date >= '2020-01-01 00:00:00'::timestamp without time zone) AND (date <= '2020-02-01 00:00:00'::timestamp without time zone))                           | |              Filter: (currency = 'EUR'::bpchar)                                                                                                                                    | |              Rows Removed by Filter: 31842                                                                                                                                         | |              Buffers: shared hit=898                                                                                                                                               | |Planning Time: 0.740 ms                                                                                                                                                             | |JIT:                                                                                                                                                                                | |  Functions: 15                                                                                                                                                                     | |  Options: Inlining true, Optimization true, Expressions true, Deforming true                                                                                                       | |  Timing: Generation 4.954 ms, Inlining 14.249 ms, Optimization 121.115 ms, Emission 77.181 ms, Total 217.498 ms                                                                    | |Execution Time: 10345.662 ms                                                                                                                                                        | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 

Server spec:

  • AMD 64 Threads
  • 315GB Ram
  • 6xSSD RAID 10 Postgres Config:
postgresql_autovacuum_vacuum_scale_factor: 0.4 postgresql_checkpoint_completion_target: 0.9 postgresql_checkpoint_timeout: 10min postgresql_effective_cache_size: 240GB postgresql_maintenance_work_mem: 2GB postgresql_random_page_cost: 1.0 postgresql_shared_buffers: 80GB postgresql_synchronous_commit: local postgresql_work_mem: 1GB 

“Percona-XtraDB-Cluster prohibits use of ALTER command on a table () that resides in non-transactional storage engine”

I installed a Percona XtraDB cluster with pxc_strict_mode set to ENFORCING, and am trying to import databases from non-Galera MariaDB servers. This works fine for some databases, by dumping them via mysqldump --skip-locks -K and importing them via mysql <database> < <dumpfile> (after creating a blank database using CREATE DATABASE). One database however is refused the import:

ERROR 1105 (HY000) at line 40: Percona-XtraDB-Cluster prohibits use of ALTER command on a table (example.example) that resides in non-transactional storage engine (except switching to transactional engine) with pxc_strict_mode = ENFORCING or MASTER 

Most internet threads simply suggest temporarily disabling pxc_strict_mode. Given however that other databases import without any issues, I assume the issue should be resolvable by altering either the the flags appended to mysqldump, or modifying the statements inside the dumpfile. I attempted removing all ALTER statements in the dump, which changes the above error to ...prohibits the use of DML command....

Both, the source MariaDB instance, as well as the new XtraDB Cluster, are configured with the default InnoDB storage engine.

Would appreciate any input.

How to create table from two alias tables with sql

I want to create a table that shows a name, position week1, position week2. I created the following queries that give the correct result for each week separately. All the data is in one table results. Server version: 10.4.14-MariaDB

 SELECT     name, team, points,week,      @curRank := @curRank + 1 AS position     FROM results,  (SELECT @curRank := 0) r     WHERE week = 1     order by points DESC 

and

SELECT     name, team, points,week,      @prevRank := @prevRank + 1 AS position2     FROM results,  (SELECT @prevRank := 0) r2     WHERE week = 2 ORDER BY points DESC 

But when I combine the with UNION I get an incorrect result.

(SELECT     name, team, points,week,      @curRank := @curRank + 1 AS position     FROM results,  (SELECT @curRank := 0) r     WHERE week = 1)       UNION  (SELECT     name, team, points,week,      @secondRank := @secondRank + 1 AS position2     FROM results,  (SELECT @secondRank := 0) r2     WHERE week = 2) ORDER BY points 

So how would I combine the two select statements to get the table with just name, position week1, position week2? I do not need points in the table, but points are used to calculate the position.

How to get count of an object, through 3 different tables in postgres with ID’s stored in each table

I’m currently using Postgres 9.6.16.

I am currently using 3 different tables to store a hypothetical users details.

The first table, called contact, this contains:

ID, Preferred_Contact_Method 

The second table, called orders, This contains:

ID, UserID, Contact_ID (the id of a row, in the contact table that relates to this order) 

The Third Table, Called order_details

ID, Orders_ID (the id in the orders table that relates to this order details) 

The tables contain other data as well, but for minimal reproduction, these are the columns that are relevant to this question.

I am trying to return some data so that i can generate a graph, in this hypothetical store, There’s only three ways we can contact a user: Email, SMS, or Physical Mail.

The graph is supposed to be 3 numbers, how many mails, emails, and SMS we’ve sent to the user; since in this hypothetical store whenever you purchase something you get notified of the successful shipment, these methods are 1:1 to the order_details, so if there’s 10 order_detail rows for the same user, then we sent 10 tracking numbers, and since there can be multiple order_details (each item has a different row in order_details) in an order, we can get the count by counting the total rows of order details belonging to a single user/contact, then attributing to what kind of contact method that user preferred at the time of making that order.

To represent this better: If a new user makes a new order, and orders 1 apple, 1 banana, and 1 orange. For the apple, the user set preferred tracking number delivery as SMS, for the banana, they set it to EMAIL, for the orange, they thought it would be funny to set the tracking number delivery via MAIL. Now, i want to generate a graph to this users preferred delivery method. So i’d like to query all those rows and obtain:

SMS, 1 EMAIL, 1 MAIL, 1 

Here’s a SQL Fiddle link with the schema and test data: http://sqlfiddle.com/#!17/eb8c0

the response with the above dataset should look like this:

method | count SMS,     4 EMAIL,   4 MAIL,    4