I'm using mariadb.

I have a single table that keeps track of video_count and image_count for a few different sites. I.e.

 type        | enum('SITE1','SITE2','SITE3')                                
 video_count | int(11)                                                
 image_count | int(11) 

video_count will exist for ALL sites but image_count only exists for SITE3. Also, SITE3 will be a minority of rows.

So does it make sense to arrange the data this way on one table? I'd like to query ALL rows...but is it a problem if it's redundant and expensive knowing that 90% of my rows will have 0 for image_count?

It'd be perfect if I could do something like SELECT * from TABLE BUT DON't GET video_count WHERE type != 'SITE3'"

Is that even a possible query in SQL? Or does it now make sense to create separate tables for 'SITE1...SITEN'? Ty!

As mustaccio pointed out, a local path is not going to be accessible from the SQL Server, which is the error you're receiving regarding "D:/My_Files/my_file.txt. It's looking for a file on the drive on the server that your SQL instance is installed on (not your local computer's drive).

You may be able to create a network share to that folder and reference it by UNC path in the SQL Server instance though. How to do that is more so in the scope of a https://serverfault.com/ question.

Alternatively you can add code in your Java program to read the local file and then do a BULK INSERT command from the app to your database.

To obtain results per part, you obviously need to group by part somewhere, and I don't see that in your attempt.

Something like this might work for you:

select 
 g.part, 
 min_cost, 
 (select max(p1.date_received) from part p1 
  where p1.part = g.part and p1.cost = g.min_cost) min_cost_date,
 max_cost, 
 (select max(p2.date_received) from part p2 
  where p2.part = g.part and p2.cost = g.max_cost) max_cost_date
from (select part, min(cost) min_cost, max(cost) max_cost
from part group by part) g

By the way, things like 70/1300/100 don't look like integer values.

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=af758528512301f3445e02674a6f834e .

I have setup a distibruted availability group and trying to understand behaviour of backups on it. While I can take backup from primary replica of Primary AG, I can not take backup from primary replica from Secondary(Forwarder) AG. https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-backup-on-secondary-replicas-always-on-availability-groups?view=sql-server-ver16#SupportedBuTypes says I should be able to.

In a distributed availability group, backups can be performed on secondary replicas in the same availability group as the active primary replica, or on the primary replica of any secondary availability groups. Backups cannot be performed on a secondary replica in a secondary availability group because secondary replicas only communicate with the primary replica in their own availability group. Only replicas that communicate directly with the global primary replica can perform backup operations.

Backup Command

BACKUP DATABASE [mydb] TO DISK = 'C:\TEMP\mydb_Full.BAK' WITH COMPRESSION

Error I am getting is -

Msg 3059, Level 16, State 1, Line 1
This BACKUP or RESTORE command is not supported on a database mirror or secondary replica.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Is this bug or I am misunderstanding something?

Backup preference is set to 'Primary' on both AG.

select sys.fn_hadr_backup_is_preferred_replica('mydb')

returns '1' on primary replicas of both Availability groups.

It will not depend on the database compatibility level.

Compatibility level (I know you mentioned mode and I assume you meant level) is a database level setting and not server level.

The database compatibility level setting provides backward compatibility with earlier versions of SQL Server in what relates to Transact-SQL and query optimization behaviors only for the specified database, not for the entire server.

Database compatibility level also provides backward compatibility with earlier versions of SQL Server, because databases attached or restored from any earlier version of SQL Server retain their existing compatibility level (if same or higher than the minimum allowed compatibility level). This was discussed in the Using compatibility level for backward compatibility section of this article.

Ref: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15

I'm in the process of installing applications and databases.

In order to check whether a file is correct, I can simply take the checksum.
Is there a way to check if a table is correct? (I realise I can drop the entire table into a file and take the checksum of that file, but maybe there's a simpler way.)

Thanks to Ergest Basha who answered this in a comment:

Here is the result:

DROP TABLE dbo.TEST_FLOAT;
CREATE TABLE dbo.TEST_FLOAT (
    AMOUNT    FLOAT
);
INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (11546.25);
SELECT AMOUNT, 
       CAST(AMOUNT AS VARCHAR(10)) AS AMOUNT_AS_CHAR,
       CONVERT (VARCHAR(10), AMOUNT, 128) AS AMOUNT_AS_CHAR2
FROM dbo.TEST_FLOAT

This is now working in AMOUNT_AS_CHAR2

UPDATE

I just discovered this solution DOES NOT WORK when the amount is ZERO. My work around is as follows:

DROP TABLE dbo.TEST_FLOAT;
CREATE TABLE dbo.TEST_FLOAT (
    AMOUNT    FLOAT
);
INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (11546.25);
INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (0.0);
INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (-0.1);
INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (2.0);
INSERT INTO dbo.TEST_FLOAT (AMOUNT) VALUES (NULL);
SELECT AMOUNT,

CAST(AMOUNT AS VARCHAR(10)) AS AMOUNT_AS_CHAR,

CONVERT(VARCHAR(10), AMOUNT, 128) AS AMOUNT_AS_CHAR2,

CASE [AMOUNT] WHEN 0 THEN '0' ELSE CONVERT(VARCHAR(59), [AMOUNT], 128) END AS AMOUNT_AS_CHAR3

FROM dbo.TEST_FLOAT

Adding the INLINE CASE wrapper gets around the issue...

we have this query and it takes more than 12 minutes with some of our users is there any hint we can improve it.

SELECT b.id,
        ARRAY_AGG(DISTINCT CONCAT(pci2.id, '~',
                                  pci2.status_id, '~',
                                  pci2.update_date, '~',
                                  pci2.insert_date, '~',
                                  pc2.unit_id ,'~',
                                  units.unit_type_id)) as project_checklists_instances_id,
        b.description,
        '2' as checklist_type,
        assign_to_all_project,
        b.last_update_date as update_date,
        CASE WHEN completed*100/count_items_times_units > 0 and completed*100/count_items_times_units < 1
               THEN 1
             WHEN abs( round(completed*100/count_items_times_units) - (completed*100/count_items_times_units) ) = 0.5
               THEN ceil(completed*100/count_items_times_units)
             ELSE round(completed*100/count_items_times_units)
        END as percentage,
        unit_type_name,
        unit_type_ids,
        unit_ids,
        array_length(unit_type_ids, 1) as unit_type_number,
        CASE WHEN failed > 0 THEN 3                                               -- Open
             WHEN count_items_times_units = completed and not_started = 0 THEN 6  -- Completed
             WHEN failed = 0 and completed > 0 THEN 2                             -- In Progress
             ELSE 1                                                               -- Not Started
        END AS status_id,
        ( SELECT user_name FROM view_project_user_name
          WHERE project_id = :projectId   -- FIXME: See note above about this fragment; also update_date and insert_dates may be incorrectly mixed --
                AND user_id = (select (CASE WHEN MAX(pci3.update_date) IS NOT NULL AND MAX(pci3.update_date) > MAX(pc3.update_date)
                                            THEN array_remove(array_agg( pci3.user_id order by pci3.insert_date desc ), NULL)
                                          ELSE array_remove(array_agg( pc3.user_id order by pc3.insert_date desc ), null) END)[1]
                               from project_checklists pc3
                                    left join project_checklists_instances pci3
                                                on pc3.id = pci3.project_checklist_id and not pci3.is_deleted
                               where pc3.project_id = :projectId
                                     and pc3.checklist_id = b.id
                                     and not pc3.is_deleted )
          LIMIT 1 ) as last_updated_by
 from (
        SELECT id,
               description,
               assign_to_all_project,
               max(update_date)                     as last_update_date,
               sum(count_items_times_units)         as count_items_times_units,
               array_agg(DISTINCT unit_type_name)   as unit_type_name,
               array_agg(DISTINCT unit_type_ids)    as unit_type_ids,
               array_agg(DISTINCT unit_id)          as unit_ids,
               sum(count_not_started)               as not_started,
               sum(count_completed)                 as completed,
               sum(count_failed)                    as failed
        from (
            SELECT pc.checklist_id as id,
                   assign_to_all_project,
                   c.description,
                   GREATEST( MAX(pci.update_date), MAX(pc.update_date) )                               AS update_date,
                   GREATEST( COUNT(DISTINCT ir.item_id), 1 )                                            AS count_items,
                   CASE WHEN pci.id is null THEN COUNT(DISTINCT pc.id)*COUNT(DISTINCT i.id)
                        ELSE COUNT(DISTINCT pc.id)*COUNT(DISTINCT ir.item_id) END                      AS count_items_times_units,
                   CASE WHEN pci.id is null THEN COUNT(DISTINCT pc.id)*COUNT(DISTINCT i.id)
                        ELSE COUNT(DISTINCT CASE WHEN ir.status_id = 0 THEN ir.id ELSE NULL END) END    AS count_not_started,
                   COUNT( DISTINCT CASE WHEN ir.status_id = 2 THEN ir.id ELSE NULL END )                AS count_completed,
                   COUNT( DISTINCT CASE WHEN ir.status_id = 1 THEN ir.id ELSE NULL END )                AS count_failed,
                   COALESCE(project_unit_type.name, 'unassigned')                                       as unit_type_name,
                   project_unit_type.id                                                                 as unit_type_ids,
                   pc.unit_id
            FROM project_checklists pc
                 JOIN checklists c ON c.id = pc.checklist_id AND NOT c.is_deleted
                 LEFT JOIN project_checklists_instances pci
                           ON pci.project_checklist_id = pc.id
                              AND pci.insert_date = (
                                SELECT MAX(pci2.insert_date)
                                FROM project_checklists_instances pci2
                                WHERE pci.project_checklist_id = pci2.project_checklist_id
                                      and not pci2.is_deleted
                              )
                              AND NOT pci.is_deleted
                 LEFT JOIN items_reports ir ON ir.checklists_instances_id = pci.id AND NOT ir.is_deleted
                 JOIN items i ON i.checklist_id = c.id AND NOT i.is_deleted
                 LEFT JOIN unit_type_checklists utc on utc.project_id = pc.project_id
                                                       AND utc.checklist_id = pc.checklist_id
                                                       and not utc.is_deleted
                 JOIN units on units.id = pc.unit_id and not units.is_deleted
                 left join project_unit_type on project_unit_type.id = units.unit_type_id
                                                and project_unit_type.project_id = pc.project_id
            WHERE NOT pc.is_deleted
                  AND c.type = 2
                  AND pc.project_id = :projectId
                  AND c.company_id = :companyId
                  AND (:checklistId::UUID IS NULL OR c.id = :checklistId)
            GROUP BY pc.checklist_id, pci.id, c.description, assign_to_all_project, project_unit_type.id,
                     pc.unit_id, project_unit_type
            order by update_date desc
          ) a
        group by id, description, assign_to_all_project
      ) b
      join project_checklists pc2 on pc2.checklist_id = b.id
                                     and pc2.project_id = :projectId
                                     and not pc2.is_deleted
      LEFT JOIN project_checklists_instances pci2 ON pci2.project_checklist_id = pc2.id
                                                     AND not pci2.is_deleted
      join units on units.id = pc2.unit_id
                    and not units.is_deleted
 group by b.id, b.description, assign_to_all_project, last_update_date, completed, count_items_times_units,
          unit_type_name, unit_type_ids, unit_ids, failed, not_started
 ORDER BY b.description

I run EXPLAIN (ANALYZE, BUFFERS) before my query and here is the result

GroupAggregate  (cost=48776.04..67239.10 rows=400 width=417) (actual time=374955.807..375018.143 rows=30 loops=1)
"  Group Key: a.description, a.id, a.assign_to_all_project, (max(a.update_date)), (sum(a.count_completed)), (sum(a.count_items_times_units)), (array_agg(DISTINCT a.unit_type_name)), (array_agg(DISTINCT a.unit_type_ids)), (array_agg(DISTINCT a.unit_id)), (sum(a.count_failed)), (sum(a.count_not_started)), ((SubPlan 2)), (array_length((array_agg(DISTINCT a.unit_type_ids)), 1))"
"  Buffers: shared hit=20896014 read=126 dirtied=1, temp read=4859611 written=4863688"
  I/O Timings: read=0.377
  ->  Sort  (cost=48776.04..48777.77 rows=692 width=377) (actual time=374955.635..374991.972 rows=3071 loops=1)
"        Sort Key: a.description, a.id, a.assign_to_all_project, (max(a.update_date)), (sum(a.count_completed)), (sum(a.count_items_times_units)), (array_agg(DISTINCT a.unit_type_name)), (array_agg(DISTINCT a.unit_type_ids)), (array_agg(DISTINCT a.unit_id)), (sum(a.count_failed)), (sum(a.count_not_started)), ((SubPlan 2)), (array_length((array_agg(DISTINCT a.unit_type_ids)), 1))"
        Sort Method: external merge  Disk: 17680kB
"        Buffers: shared hit=20896014 read=126 dirtied=1, temp read=4859611 written=4863688"
        I/O Timings: read=0.377
        ->  Nested Loop  (cost=9880.60..48743.40 rows=692 width=377) (actual time=372649.013..374899.612 rows=3071 loops=1)
"              Buffers: shared hit=20896014 read=126 dirtied=1, temp read=4857401 written=4861473"
              I/O Timings: read=0.377
              ->  Hash Right Join  (cost=9880.17..12081.51 rows=745 width=325) (actual time=372647.685..372692.136 rows=3071 loops=1)
                    Hash Cond: (pci2.project_checklist_id = pc2.id)
"                    Buffers: shared hit=17319532 read=126 dirtied=1, temp read=4857401 written=4861473"
                    I/O Timings: read=0.377
                    ->  Seq Scan on project_checklists_instances pci2  (cost=0.00..2001.68 rows=52981 width=52) (actual time=0.011..12.283 rows=52975 loops=1)
                          Filter: (NOT is_deleted)
                          Rows Removed by Filter: 2994
                          Buffers: shared hit=1316 read=126 dirtied=1
                          I/O Timings: read=0.377
                    ->  Hash  (cost=9870.86..9870.86 rows=745 width=305) (actual time=372642.438..372642.450 rows=3042 loops=1)
                          Buckets: 1024 (originally 1024)  Batches: 8 (originally 1)  Memory Usage: 4095kB
"                          Buffers: shared hit=17318216, temp read=4854162 written=4860139"
                          ->  Nested Loop  (cost=3042.42..9870.86 rows=745 width=305) (actual time=372617.334..372624.194 rows=3042 loops=1)
"                                Buffers: shared hit=17318216, temp read=4854162 written=4858234"
                                ->  GroupAggregate  (cost=3042.00..3143.83 rows=574 width=273) (actual time=372617.302..372621.184 rows=30 loops=1)
"                                      Group Key: a.id, a.description, a.assign_to_all_project"
"                                      Buffers: shared hit=17314788, temp read=4854162 written=4858234"
                                      ->  Sort  (cost=3042.00..3049.17 rows=2868 width=145) (actual time=372616.765..372616.984 rows=3042 loops=1)
"                                            Sort Key: a.id, a.description, a.assign_to_all_project"
                                            Sort Method: quicksort  Memory: 868kB
"                                            Buffers: shared hit=17314788, temp read=4854162 written=4858234"
                                            ->  Subquery Scan on a  (cost=2841.44..2877.29 rows=2868 width=145) (actual time=372614.924..372615.779 rows=3042 loops=1)
"                                                  Buffers: shared hit=17314788, temp read=4854162 written=4858234"
                                                  ->  Sort  (cost=2841.44..2848.61 rows=2868 width=266) (actual time=372614.921..372615.194 rows=3042 loops=1)
"                                                        Sort Key: (GREATEST(max(pci.update_date), max(pc.update_date))) DESC"
                                                        Sort Method: quicksort  Memory: 1013kB
"                                                        Buffers: shared hit=17314788, temp read=4854162 written=4858234"
                                                        ->  GroupAggregate  (cost=2468.81..2676.74 rows=2868 width=266) (actual time=329651.160..372611.465 rows=3042 loops=1)
"                                                              Group Key: pc.checklist_id, pci.id, c.description, c.assign_to_all_project, project_unit_type.id, pc.unit_id"
"                                                              Buffers: shared hit=17314788, temp read=4854162 written=4858234"
                                                              ->  Sort  (cost=2468.81..2475.98 rows=2868 width=278) (actual time=329637.576..347518.000 rows=27790661 loops=1)
"                                                                    Sort Key: pc.checklist_id, pci.id, c.description, c.assign_to_all_project, project_unit_type.id, pc.unit_id"
                                                                    Sort Method: external merge  Disk: 7623392kB
"                                                                    Buffers: shared hit=17314788, temp read=4769820 written=4773550"
                                                                    ->  Nested Loop  (cost=11.32..2304.10 rows=2868 width=278) (actual time=0.125..13827.202 rows=27790661 loops=1)
                                                                          Join Filter: (c.id = i.checklist_id)
                                                                          Buffers: shared hit=17314788
                                                                          ->  Nested Loop Left Join  (cost=10.91..1699.04 rows=153 width=278) (actual time=0.112..1075.388 rows=893928 loops=1)
                                                                                Buffers: shared hit=883722
                                                                                ->  Nested Loop Left Join  (cost=6.37..489.49 rows=11 width=242) (actual time=0.107..420.784 rows=141935 loops=1)
                                                                                      Join Filter: (project_unit_type.project_id = pc.project_id)
                                                                                      Buffers: shared hit=601812
                                                                                      ->  Nested Loop Left Join  (cost=6.09..486.04 rows=11 width=153) (actual time=0.088..160.635 rows=141935 loops=1)
                                                                                            Buffers: shared hit=176007
                                                                                            ->  Nested Loop Left Join  (cost=5.81..477.82 rows=11 width=153) (actual time=0.076..41.911 rows=3042 loops=1)
                                                                                                  Buffers: shared hit=28201
                                                                                                  ->  Nested Loop  (cost=5.39..345.28 rows=11 width=129) (actual time=0.065..22.460 rows=3042 loops=1)
                                                                                                        Buffers: shared hit=15622
                                                                                                        ->  Nested Loop  (cost=4.97..267.63 rows=12 width=113) (actual time=0.052..5.836 rows=3042 loops=1)
                                                                                                              Buffers: shared hit=3454
                                                                                                              ->  Bitmap Heap Scan on checklists c  (cost=4.55..55.80 rows=17 width=41) (actual time=0.028..0.079 rows=33 loops=1)
                                                                                                                    Recheck Cond: (company_id = '9d29d3e5-7c92-e462-bf13-154357d0a12e'::uuid)
                                                                                                                    Filter: ((NOT is_deleted) AND (type = 2))
                                                                                                                    Rows Removed by Filter: 2
                                                                                                                    Heap Blocks: exact=9
                                                                                                                    Buffers: shared hit=11
                                                                                                                    ->  Bitmap Index Scan on checklists_company_id_index  (cost=0.00..4.54 rows=35 width=0) (actual time=0.019..0.019 rows=90 loops=1)
                                                                                                                          Index Cond: (company_id = '9d29d3e5-7c92-e462-bf13-154357d0a12e'::uuid)
                                                                                                                          Buffers: shared hit=2
                                                                                                              ->  Index Scan using project_checklists_checklist_id_project_id_unit_id_pk on project_checklists pc  (cost=0.42..12.45 rows=1 width=72) (actual time=0.011..0.140 rows=92 loops=33)
                                                                                                                    Index Cond: ((checklist_id = c.id) AND (project_id = '72d38163-5fc7-94fb-c1fb-8a095e40441b'::uuid))
                                                                                                                    Filter: (NOT is_deleted)
                                                                                                                    Rows Removed by Filter: 9
                                                                                                                    Buffers: shared hit=3443
                                                                                                        ->  Index Scan using units_pkey on units units_1  (cost=0.42..6.47 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=3042)
                                                                                                              Index Cond: (id = pc.unit_id)
                                                                                                              Filter: (NOT is_deleted)
                                                                                                              Buffers: shared hit=12168
                                                                                                  ->  Index Scan using project_checklists_instances_project_id_checklist_id_instance_n on project_checklists_instances pci  (cost=0.41..12.04 rows=1 width=40) (actual time=0.005..0.005 rows=0 loops=3042)
                                                                                                        Index Cond: (project_checklist_id = pc.id)
                                                                                                        Filter: ((NOT is_deleted) AND (insert_date = (SubPlan 3)))
                                                                                                        Rows Removed by Filter: 0
                                                                                                        Buffers: shared hit=12579
                                                                                                        SubPlan 3
                                                                                                          ->  Aggregate  (cost=8.43..8.44 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=667)
                                                                                                                Buffers: shared hit=2750
                                                                                                                ->  Index Scan using project_checklists_instances_project_id_checklist_id_instance_n on project_checklists_instances pci2_1  (cost=0.41..8.43 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=667)
                                                                                                                      Index Cond: (pci.project_checklist_id = project_checklist_id)
                                                                                                                      Filter: (NOT is_deleted)
                                                                                                                      Rows Removed by Filter: 0
                                                                                                                      Buffers: shared hit=2750
                                                                                            ->  Index Scan using unit_type_checklists_checklist_id_project_id_unit_type_id_uk on unit_type_checklists utc  (cost=0.28..0.74 rows=1 width=32) (actual time=0.005..0.031 rows=47 loops=3042)
                                                                                                  Index Cond: ((checklist_id = pc.checklist_id) AND (project_id = pc.project_id) AND (project_id = '72d38163-5fc7-94fb-c1fb-8a095e40441b'::uuid))
                                                                                                  Filter: (NOT is_deleted)
                                                                                                  Rows Removed by Filter: 0
                                                                                                  Buffers: shared hit=147806
                                                                                      ->  Index Scan using project_unit_type_id_pk on project_unit_type  (cost=0.28..0.30 rows=1 width=137) (actual time=0.001..0.001 rows=1 loops=141935)
                                                                                            Index Cond: (id = units_1.unit_type_id)
                                                                                            Filter: (project_id = '72d38163-5fc7-94fb-c1fb-8a095e40441b'::uuid)
                                                                                            Buffers: shared hit=425805
                                                                                ->  Bitmap Heap Scan on items_reports ir  (cost=4.54..109.62 rows=34 width=52) (actual time=0.001..0.003 rows=6 loops=141935)
                                                                                      Recheck Cond: (checklists_instances_id = pci.id)
                                                                                      Filter: (NOT is_deleted)
                                                                                      Heap Blocks: exact=183730
                                                                                      Buffers: shared hit=281910
                                                                                      ->  Bitmap Index Scan on items_reports_checklists_instances_id_index  (cost=0.00..4.53 rows=34 width=0) (actual time=0.001..0.001 rows=6 loops=141935)
                                                                                            Index Cond: (checklists_instances_id = pci.id)
                                                                                            Buffers: shared hit=98180
                                                                          ->  Index Scan using items_checklist_id_index on items i  (cost=0.41..3.70 rows=20 width=32) (actual time=0.002..0.010 rows=31 loops=893928)
                                                                                Index Cond: (checklist_id = pc.checklist_id)
                                                                                Filter: (NOT is_deleted)
                                                                                Rows Removed by Filter: 0
                                                                                Buffers: shared hit=16431066
                                ->  Index Scan using project_checklists_checklist_id_project_id_unit_id_pk on project_checklists pc2  (cost=0.42..11.70 rows=1 width=48) (actual time=0.010..0.082 rows=101 loops=30)
                                      Index Cond: ((checklist_id = a.id) AND (project_id = '72d38163-5fc7-94fb-c1fb-8a095e40441b'::uuid))
                                      Filter: (NOT is_deleted)
                                      Rows Removed by Filter: 9
                                      Buffers: shared hit=3428
              ->  Index Scan using units_pkey on units  (cost=0.42..6.47 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=3071)
                    Index Cond: (id = pc2.unit_id)
                    Filter: (NOT is_deleted)
                    Buffers: shared hit=12284
              SubPlan 2
                ->  Limit  (cost=21.92..46.01 rows=1 width=32) (actual time=0.714..0.714 rows=1 loops=3071)
                      Buffers: shared hit=3564198
                      InitPlan 1 (returns $2)
                        ->  Aggregate  (cost=20.91..20.93 rows=1 width=16) (actual time=0.704..0.704 rows=1 loops=3071)
                              Buffers: shared hit=3533508
                              ->  Nested Loop Left Join  (cost=0.84..20.89 rows=1 width=64) (actual time=0.006..0.558 rows=272 loops=3071)
                                    Buffers: shared hit=3533508
                                    ->  Index Scan using project_checklists_checklist_id_project_id_unit_id_pk on project_checklists pc3  (cost=0.42..12.45 rows=1 width=48) (actual time=0.004..0.126 rows=269 loops=3071)
                                          Index Cond: ((checklist_id = a.id) AND (project_id = '72d38163-5fc7-94fb-c1fb-8a095e40441b'::uuid))
                                          Filter: (NOT is_deleted)
                                          Rows Removed by Filter: 2
                                          Buffers: shared hit=850202
                                    ->  Index Scan using project_checklists_instances_project_id_checklist_id_instance_n on project_checklists_instances pci3  (cost=0.41..8.43 rows=1 width=48) (actual time=0.001..0.001 rows=0 loops=826972)
                                          Index Cond: (pc3.id = project_checklist_id)
                                          Filter: (NOT is_deleted)
                                          Rows Removed by Filter: 0
                                          Buffers: shared hit=2683306
                      ->  Nested Loop Left Join  (cost=0.99..25.08 rows=1 width=32) (actual time=0.713..0.713 rows=1 loops=3071)
                            Join Filter: ((upc.user_id = u.id) AND (up.sub_company_id = upc.sub_company_id))
                            Buffers: shared hit=3564198
                            ->  Nested Loop  (cost=0.70..16.75 rows=1 width=57) (actual time=0.711..0.711 rows=1 loops=3071)
                                  Buffers: shared hit=3555000
                                  ->  Index Scan using users_pkey on users u  (cost=0.29..8.30 rows=1 width=41) (actual time=0.003..0.003 rows=1 loops=3071)
                                        Index Cond: (id = $2)
                                        Buffers: shared hit=9213
                                  ->  Index Scan using users_projects_user_id_project_id_pk on users_projects up  (cost=0.42..8.44 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=3071)
                                        Index Cond: ((user_id = $2) AND (project_id = '72d38163-5fc7-94fb-c1fb-8a095e40441b'::uuid))
                                        Buffers: shared hit=12279
                            ->  Index Scan using user_permisions_c_idx on users_permissions_companies upc  (cost=0.29..8.31 rows=1 width=61) (actual time=0.002..0.002 rows=1 loops=3066)
                                  Index Cond: (user_id = $2)
                                  Buffers: shared hit=9198
Planning Time: 23.193 ms
Execution Time: 375932.570 ms

Postgres Version 11.13

here is the sample of the result to give a better stand of what happing

[{"id":"fe68b609-0afc-5337-8316-ff605440151f","project_checklists_instances_id":["~~~~00e148bf-7316-8847-c651-1860831a62dd~","~~~~074ff140-5969-1cdf-2549-28ee790b237a~"],"description":"test item","checklist_type":"2","assign_to_all_project":false,"update_date":"2022-05-02T13:21:39.885Z","percentage":"0","unit_type_name":["unassigned"],"unit_type_ids":[null],"unit_ids":[""f7dc1ba4-a888-069c-7552-e26d0f44a90d"],"unit_type_number":1,"status_id":1,"last_updated_by":"Admin: Mina"}]

Amazon RDS Postgres database are backed up as snapshots automatically. Because of the high storage costs ($0.095 per GB-Month), I want to move them to S3 (Storage Class: Glacier Deep Archive: 0.00099 per GB-Month).

I basically followed this https://www.youtube.com/watch?v=lyNGeDg6EII on youtube to https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ExportSnapshot.html , which creates a file structure with parquet files per table.

Before I proceed, I want to make sure I'm able to restore those files in S3 as a new database instance again. I can't find options to do that within AWS. So my question is this.

How to restore a RDS Postgres instance from a snapshot exported to S3?

Take the following queries:

DECLARE @X VARCHAR(200) = '1,2,3,4'
SELECT

*,

dbo.aUserDefinedScalarFunction(4) AS ScalarValue

FROM

MyTable T

INNER JOIN dbo.aUserDefineTableFunction(@X) A ON T.SomeID=A.SomeID

WHERE

(T.ID1 IS NULL OR T.ID1 IN (SELECT [value] FROM STRING_SPLIT(@X,',')))

AND

(T.ID2 IS NULL OR T.ID2 IN (SELECT Value FROM dbo.MySplitterFunction(@X))

I normally create indexed #tempTables for the WHERE conditions above, I have found that to perform better on large datasets. However, I still can't find definitive answers to the following questions:

1. Will the query analyzer optimize aUserDefinedScalarFunction(4) As ScalarValue or does it get evaluated for each record?

2. Will the INNER JOIN dbo.aUserDefineTableFunction(@X) get materialized into a temp table once or will it be executed for each record? The function returns table (not a table variable).

3. Does the result of SELECT [value] FROM STRING_SPLIT(@X,',') get optimized or is it evaluated for each comparison?

4. Does the result of SELECT Value FROM dbo.MySplitterFunction(@X) get optimized or is it evaluated during each comparison?