Is failing over a group sufficient to bind the new certificate?

Unfortunately it is not. Failing over an AG does not restart the instance, rather it causes state changes to the AG in both the cluster and SQL Server.

Or does SQL Server have to be restarted from the Cluster Manager?

Please do not use FCM with AGs as it isn't supported and can cause there to be synchronization issues between SQL Server and the Cluster, needing to be reset by stopping all instances of SQL Server and one by one bringing them back online. Note that this is only for AGs and not for FCIs which is perfectly fine to use FCM (unless the FCI also hosts AGs).

Stop the services via SSCM, if possible, or other service interaction options such as PowerShell, .Net remoting, etc., which you could start with the secondary replicas first and then fail over, then restart the old primary.

I have two servers A and B. Both are in an Always On Group. The B server is configured for ReadOnly requests. This works fine so far, but recently there is a problem for queries for a service.

The service first makes an update to a table (probably on the A server) and then a select with Read Only on the B server. The developer says these are two separate transactions.

Unfortunately, the select does not return the values with which an update was previously made. Only after about 1 to 1.5 seconds do the correct values appear. We have never been able to observe this behaviour before.

The server is set to synchronous commit. All databases are synchronous and have no data loss. The routing for read only has been tested and works. Readable Secondary is set to "Yes".

I've only been working as a DBA for MSSQL databases for about 1.5 years and I don't quite understand how the two nodes A and B communicate in an AG, but I assumed that both nodes would receive the data at the same time when using "synchronous commit", right?

What could be the reason for this 1 to 1.5 second time difference?

Servers are SQL 2019 Enterprise with CU12 - Running on some powerful physical machines with Windows Server 2016 Standard.

We have a legacy TokuDB database that we would like to begin migrating to a new engine. Unfortunately taking the db offline for migration is not feasible. We also can reprocess all the data in the database eventually. Is it possible in MySQL to replicate new without syncing the extant data first?

Problem

The following query produces a really inefficient query plan:

select `event`.`id` as `eventId` 
from `alert`
inner join `event` on `event`.`id` = `alert`.`eventId` 
inner join `property` on `property`.`id` = `event`.`propertyId`
inner join `landlord` on `landlord`.`id` = `property`.`landlordId`
where `landlord`.`reference` in ( 
     'landlord1',
     'landlord2',
     'landlord3',
     'landlord4',
     'landlord5',
     'landlord6',
     'landlord7',   
     'landlord8'
)
order by `alert`.`raiseddate` desc limit 20;

Query plan:

The index of note here is alert_eventIdRaisedDate on Alert(`eventId`,`raisedDate` DESC)

Timing: 4008ms

By forcing mysql to ignore a specific index, things are much much faster.

select `event`.`id` as `eventId` 
from `alert` ignore index (alert_eventIdRaisedDate)
inner join `event` on `event`.`id` = `alert`.`eventId` 
inner join `property` on `property`.`id` = `event`.`propertyId`
inner join `landlord` on `landlord`.`id` = `property`.`landlordId`
where `landlord`.`reference` in ( 
     'landlord1',
     'landlord2',
     'landlord3',
     'landlord4',
     'landlord5',
     'landlord6',
     'landlord7',   
     'landlord8'
)
order by `alert`.`raiseddate` desc limit 20;

Query plan:

Timing: 83ms

If we execute the query without a landlord where clause, no hint is required and it's really fast.

select `event`.`id` as `eventId` 
from `alert` 
inner join `event` on `event`.`id` = `alert`.`eventId` 
inner join `property` on `property`.`id` = `event`.`propertyId`
inner join `landlord` on `landlord`.`id` = `property`.`landlordId`
order by `alert`.`raiseddate` desc limit 20;

Query Plan:

Timing: 18ms

Thoughts

I believe the performance penalty without the index hint is incurred due to using temporary, using file sort which means mysql is not able to satisfy the order by clause from an index. alert_eventIdRaisedDate on Alert(`eventId`,`raisedDate` DESC), so why is my sql choosing this index and what can be done to either the query, schema or indexes to remove the need for the ignore index hint?

I have tested this on both mysql 5.7 and mysql 8, same same. Details captured in this question are from mysql 8.0

Schema

CREATE TABLE `alert` (
  `id` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `value` decimal(10,4) DEFAULT NULL,
  `valueUnitId` varchar(255) DEFAULT NULL,
  `eventId` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `test` varchar(255) DEFAULT NULL,
  `typeId` varchar(255) NOT NULL,
  `statusId` varchar(255) NOT NULL,
  `raisedDate` datetime(3) NOT NULL,
  `resolvedDate` datetime(3) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `snoozedUntil` datetime(3) DEFAULT NULL,
  `autoCancel` tinyint(1) NOT NULL DEFAULT '0',
  `resolvingEventId` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `eventId_UNIQUE` (`eventId`) /*!80000 INVISIBLE */,
  UNIQUE KEY `alert_EventIdTypeIdStatusIdUnique` (`eventId`,`typeId`,`statusId`) /*!80000 INVISIBLE */,
  KEY `valueUnitId` (`valueUnitId`),
  KEY `test` (`test`),
  KEY `typeId` (`typeId`),
  KEY `alert_resolvingEventId_foreign_idx` (`resolvingEventId`),
  KEY `idx_alert_raisedDateEventIdStatusId` (`raisedDate`,`statusId`,`eventId`),
  KEY `alert_raisedDate` (`raisedDate`),
  KEY `alert_eventIdStatusId` (`eventId`,`statusId`) /*!80000 INVISIBLE */,
  KEY `alert_idEventIdStatusId` (`id`,`eventId`,`statusId`),
  KEY `alert_eventIdId` (`eventId`,`id`) /*!80000 INVISIBLE */,
  KEY `alert_idEventId` (`id`,`eventId`),
  KEY `alert_statusIdTypeIdEventId` (`statusId`,`typeId`,`eventId`),
  KEY `alert_EventIdTypeIdStatusId` (`eventId`,`typeId`,`statusId`) /*!80000 INVISIBLE */,
  KEY `alert_eventIdRaisedDate` (`eventId`,`raisedDate` DESC),
  KEY `alert_raisedDateEventId` (`raisedDate` DESC,`eventId`),
  CONSTRAINT `alert_ibfk_1` FOREIGN KEY (`valueUnitId`) REFERENCES `measurementunit` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `alert_ibfk_2` FOREIGN KEY (`eventId`) REFERENCES `event` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `alert_ibfk_3` FOREIGN KEY (`test`) REFERENCES `measurementunit` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `alert_ibfk_4` FOREIGN KEY (`typeId`) REFERENCES `alerttype` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `alert_ibfk_5` FOREIGN KEY (`statusId`) REFERENCES `alertstatus` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `alert_resolvingEventId_foreign_idx` FOREIGN KEY (`resolvingEventId`) REFERENCES `event` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and

CREATE TABLE `event` (
  `id` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `eventTypeId` varchar(255) NOT NULL,
  `sourceId` varchar(255) NOT NULL,
  `deviceId` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `propertyId` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `raisedDate` datetime(3) NOT NULL,
  `insightId` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `raisedYear` int GENERATED ALWAYS AS (year(`raisedDate`)) STORED,
  `raisedMonth` int GENERATED ALWAYS AS (month(`raisedDate`)) STORED,
  `raisedDay` int GENERATED ALWAYS AS (dayofmonth(`raisedDate`)) STORED,
  PRIMARY KEY (`id`),
  UNIQUE KEY `event_UNIQUE` (`deviceId`,`insightId`,`propertyId`,`raisedDate`,`eventTypeId`) /*!80000 INVISIBLE */,
  UNIQUE KEY `event_DEVICE` (`deviceId`,`raisedDate`,`eventTypeId`) /*!80000 INVISIBLE */,
  UNIQUE KEY `event_INSIGHT` (`insightId`,`raisedDate`,`eventTypeId`) /*!80000 INVISIBLE */,
  KEY `sourceId` (`sourceId`) /*!80000 INVISIBLE */,
  KEY `idx_eventtypeId_id` (`eventTypeId`,`id`) /*!80000 INVISIBLE */,
  KEY `idx_eventType_deviceId_raisedDate` (`eventTypeId`,`deviceId`,`raisedDate`) /*!80000 INVISIBLE */,
  KEY `event_propertyIdRaisedDate` (`propertyId`,`raisedDate`),
  KEY `event_raiseDate` (`raisedDate`),
  KEY `event_eventTypeIdraiseDate` (`eventTypeId`,`raisedDate`) /*!80000 INVISIBLE */,
  KEY `event_eventTypeIdPropertyidraiseDate` (`eventTypeId`,`propertyId`,`raisedDate`),
  KEY `event_raisedDatePropertyId` (`raisedDate`,`propertyId`) /*!80000 INVISIBLE */,
  KEY `event_raisedDateDESC` (`raisedDate` DESC),
  KEY `event_propertyIdRaisedDateDESC` (`propertyId`,`raisedDate` DESC),
  CONSTRAINT `event_ibfk_1` FOREIGN KEY (`eventTypeId`) REFERENCES `eventtype` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `event_ibfk_2` FOREIGN KEY (`sourceId`) REFERENCES `source` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `event_ibfk_3` FOREIGN KEY (`deviceId`) REFERENCES `device` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `event_ibfk_4` FOREIGN KEY (`propertyId`) REFERENCES `property` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `event_insightId_foreign_idx` FOREIGN KEY (`insightId`) REFERENCES `insight` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and

CREATE TABLE `property` (
  `id` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `name` varchar(255) NOT NULL,
  `reference` varchar(255) NOT NULL,
  `parentId` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `landlordId` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `addressId` int NOT NULL,
  `geoId` int NOT NULL,
  `typeId` varchar(255) NOT NULL,
  `statusId` varchar(255) NOT NULL,
  `constructionTypeId` varchar(255) DEFAULT NULL,
  `heatingTypeId` varchar(255) DEFAULT NULL,
  `constructionDate` datetime(3) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `propertyFloorId` varchar(255) DEFAULT NULL,
  `buildingTypeId` varchar(255) DEFAULT NULL,
  `isStock` bit(1) DEFAULT NULL,
  `createdBy` varchar(255) NOT NULL DEFAULT 'SYSTEM',
  `displayReference` varchar(255) DEFAULT NULL,
  `deletedBy` varchar(255) DEFAULT NULL,
  `designationId` varchar(255) NOT NULL DEFAULT 'DOMESTIC',
  PRIMARY KEY (`id`),
  UNIQUE KEY `reference` (`reference`),
  UNIQUE KEY `idx_isStockUnique` (`isStock`,`landlordId`),
  KEY `parentId` (`parentId`),
  KEY `landlordId` (`landlordId`),
  KEY `addressId` (`addressId`),
  KEY `geoId` (`geoId`),
  KEY `typeId` (`typeId`),
  KEY `constructionTypeId` (`constructionTypeId`),
  KEY `heatingTypeId` (`heatingTypeId`),
  KEY `property_propertyFloorId_foreign_idx` (`propertyFloorId`),
  KEY `property_buildingTypeId_foreign_idx` (`buildingTypeId`),
  KEY `idx_propertyId` (`id`),
  KEY `idx_propertyReference` (`reference`),
  KEY `idx_propertyPropertyStatusId` (`statusId`),
  KEY `property_designationId_foreign_idx` (`designationId`),
  CONSTRAINT `property_buildingTypeId_foreign_idx` FOREIGN KEY (`buildingTypeId`) REFERENCES `buildingtype` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `property_designationId_foreign_idx` FOREIGN KEY (`designationId`) REFERENCES `propertydesignation` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `property_ibfk_1` FOREIGN KEY (`parentId`) REFERENCES `property` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `property_ibfk_2` FOREIGN KEY (`landlordId`) REFERENCES `landlord` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `property_ibfk_3` FOREIGN KEY (`addressId`) REFERENCES `address` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `property_ibfk_4` FOREIGN KEY (`geoId`) REFERENCES `geo` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `property_ibfk_5` FOREIGN KEY (`typeId`) REFERENCES `propertytype` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `property_ibfk_6` FOREIGN KEY (`statusId`) REFERENCES `propertystatus` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `property_ibfk_7` FOREIGN KEY (`constructionTypeId`) REFERENCES `constructiontype` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `property_ibfk_8` FOREIGN KEY (`heatingTypeId`) REFERENCES `heatingtype` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `property_propertyFloorId_foreign_idx` FOREIGN KEY (`propertyFloorId`) REFERENCES `propertyfloor` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and

CREATE TABLE `landlord` (
  `id` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `name` varchar(255) NOT NULL,
  `reference` varchar(255) NOT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `description` varchar(255) DEFAULT NULL,
  `totalHousingStock` int NOT NULL DEFAULT '1',
  `statusId` varchar(255) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `postcode` varchar(255) DEFAULT NULL,
  `contactName` varchar(255) DEFAULT NULL,
  `contactEmail` varchar(255) DEFAULT NULL,
  `contactPhone` varchar(255) DEFAULT NULL,
  `allowPropertyCreation` tinyint(1) NOT NULL DEFAULT '1',
  `createdBy` varchar(255) NOT NULL DEFAULT 'SYSTEM',
  `typeId` varchar(255) DEFAULT NULL,
  `parentReference` varchar(255) DEFAULT NULL,
  `designationId` varchar(255) NOT NULL DEFAULT 'PROVIDER',
  `internal` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `reference` (`reference`),
  KEY `landlord_statusId_foreign_idx` (`statusId`),
  KEY `landlord_typeId_foreign_idx` (`typeId`),
  KEY `landlord_designationId_foreign_idx` (`designationId`),
  KEY `landlord_parentreferenceId` (`parentReference`,`id`) /*!80000 INVISIBLE */,
  CONSTRAINT `landlord_designationId_foreign_idx` FOREIGN KEY (`designationId`) REFERENCES `landlorddesignation` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `landlord_parentReference_foreign_idx` FOREIGN KEY (`parentReference`) REFERENCES `landlord` (`reference`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `landlord_statusId_foreign_idx` FOREIGN KEY (`statusId`) REFERENCES `landlordstatus` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `landlord_typeId_foreign_idx` FOREIGN KEY (`typeId`) REFERENCES `landlordtype` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Below gives one item but the wrong count

SELECT COUNT(activities.id) FROM activities AS COUNT

[0] => stdClass Object
(
  [COUNT] => 189
)

Below gives multiple items but the correct count (total array items)

SELECT COUNT(activities.id) AS COUNT
GROUP BY activities.id

[0] => stdClass Object
(
  [COUNT] => 4
)
[1] => stdClass Object
(
  [COUNT] => 4
)

How can I use GROUP BY but still only get one count result? (I've tried DISTINCT without luck)

I've routinely connected to a postgres database via ODBC using a powershell script. Like this....

$DBConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=$MyServer;Port=$MyPort;Database=$MyDB;Uid=$MyUid;Pwd=$MyPass;Timeout=$timeout;CommandTimeout=$commandTimeout;"
$script:DBConn = New-Object System.Data.Odbc.OdbcConnection;
$DBConn.ConnectionString = $DBConnectionString;
$DBConn.Open();

I've just started getting the error....

Exception calling "Open" with "0" argument(s): "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

Since this script is running a new build, I'm guessing a configuration error. Server is windows 11, Postgre is a fresh installation of 14.2 downloaded from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads The latest ODBC drivers came from the file psqlodbc_13_02_0000-x64.zip also downloaded from https://www.postgresql.org/ftp/odbc/versions/msi/

I think the ODBC driver installation was successful because if I run ODBC 64 bit administrator, click on the 'drivers' tab I can see the 4 drivers I expect

1. PostgreSQL ANSI
2. PostgreSQL ANSI(x64)
3. PostgreSQL Unicode
4. PostgreSQL Unicode(x64)

I've tried editing the script to try each of the 4 drivers I think I have - but get the same error each time. I know the database is up and working, I can connect via pgAdmin and psql.

It's a new, clean installation of something that has worked on previous servers for several years. What have I forgotten or done wrong?

The script is being run from a desktop shortcut with this target (I think this demonstrates I'm running the 64 bit version)

"%SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe " -NoExit -command "&c:\pwrShl\TestToDb_015.ps1

I've tried setting up a DSN (just for test) and I the ODBC 'test' button says the connection worked. I've tried running the powershell script as administrator - makes no difference.

I have a PostgreSQL table with ~millions of rows (timestamp, event, distinct_id, properties) where I want to

• filter based on event (low cardinality) and timestamp,
• and group by date_trunc('week', timestamp) and distinct_id (high cardinality)

I have already squeezed the performance of COUNT(*) ... GROUP BY week queries, specifically these ones:

SELECT
  date_trunc('week', timestamp) AS "timestamp"
  , count(*) AS "count"
FROM telemetry_events
WHERE (
  (event = 'view load' OR event = 'view:loaded')
  AND timestamp >= timestamp with time zone '2022-01-02 00:00:00.000Z'
)
GROUP BY
  date_trunc('week', timestamp)
ORDER BY
  date_trunc('week', timestamp) ASC
;

by creating two indexes:

CREATE INDEX idx_timestamp ON public.telemetry_events USING btree ("timestamp")
CREATE INDEX telemetry_events_event_timestamp_idx ON public.telemetry_events USING btree (event, "timestamp")

The current execution time is under 7 seconds, which I find excellent (at least for now). https://explain.depesz.com/s/dTOL :

GroupAggregate  (cost=83849.24..87478.16 rows=181418 width=16) (actual time=6971.824..6989.712 rows=21 loops=1)
  Group Key: (date_trunc('week'::text, "timestamp"))
  ->  Sort  (cost=83849.24..84302.97 rows=181493 width=8) (actual time=6971.735..6978.683 rows=116425 loops=1)
        Sort Key: (date_trunc('week'::text, "timestamp"))
        Sort Method: quicksort  Memory: 8189kB
        ->  Index Only Scan using telemetry_events_event_timestamp_idx on telemetry_events  (cost=0.43..67996.23 rows=181493 width=8) (actual time=6507.580..6906.571 rows=116425 loops=1)
              Index Cond: ("timestamp" >= '2022-01-02 00:00:00+00'::timestamp with time zone)
              Filter: (((event)::text = 'view load'::text) OR ((event)::text = 'view:loaded'::text))
              Rows Removed by Filter: 639794
              Heap Fetches: 0
Planning Time: 1.873 ms
Execution Time: 6989.778 ms

However, if I add my high cardinality distinct_id to the GROUP BY, the queries are much slower (45 to 70 seconds):

SELECT
  date_trunc('week', timestamp) AS "timestamp"
  , distinct_id
  , count(*) AS "count"
FROM telemetry_events
WHERE (
  (event = 'view load' OR event = 'view:loaded')
  AND timestamp >= timestamp with time zone '2022-01-02 00:00:00.000Z'
)
GROUP BY (
  date_trunc('week', timestamp)
  , distinct_id
)
ORDER BY
  date_trunc('week', timestamp) ASC
;

This is the EXPLAIN ANALYZE:

Finalize GroupAggregate  (cost=88115.68..110676.54 rows=181418 width=52) (actual time=71956.298..72028.293 rows=1326 loops=1)
  Group Key: (date_trunc('week'::text, "timestamp")), distinct_id
  ->  Gather Merge  (cost=88115.68..107274.48 rows=151244 width=52) (actual time=71956.254..72027.304 rows=2526 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial GroupAggregate  (cost=87115.65..88817.15 rows=75622 width=52) (actual time=71740.225..71758.172 rows=842 loops=3)
              Group Key: (date_trunc('week'::text, "timestamp")), distinct_id
              ->  Sort  (cost=87115.65..87304.71 rows=75622 width=44) (actual time=71736.479..71743.974 rows=38808 loops=3)
                    Sort Key: (date_trunc('week'::text, "timestamp")), distinct_id
                    Sort Method: quicksort  Memory: 4520kB
                    Worker 0:  Sort Method: quicksort  Memory: 4507kB
                    Worker 1:  Sort Method: quicksort  Memory: 4679kB
                    ->  Parallel Index Scan using idx_timestamp on telemetry_events  (cost=0.43..80987.81 rows=75622 width=44) (actual time=402.899..71576.001 rows=38808 loops=3)
                          Index Cond: ("timestamp" >= '2022-01-02 00:00:00+00'::timestamp with time zone)
                          Filter: (((event)::text = 'view load'::text) OR ((event)::text = 'view:loaded'::text))
                          Rows Removed by Filter: 213265
Planning Time: 1.942 ms
Execution Time: 72029.136 ms

What would be the best way to try to optimize these queries?

And some sub-questions: is my expectation of keeping this query under ~10 seconds reasonable? How fast can I make PostgreSQL for these kind of OLAP workloads?

Our data and log files have increased drastically due to a recent large INSERT query and DELETE query. The database in simple recovery mode.

Does the shrink data and log file command take the database offline?

I want to understand this to schedule whether the shrink on data and log file can be done during working hours?

Try using an EXCEPT…

SELECT InstNum
FROM Table
WHERE Profile = 'C'
EXCEPT
SELECT InstNum

FROM Table

WHERE Profile <> ‘C'

GROUP BY InstNum;

I would like to show the columns of "linked" tables in SQL-Server database.
In my particular case, there are two tables "Loads" and "Locations", linked by Loads.LocationId=Locations.Id.

I just to work for a company, based on Oracle DB technology, which used the following notation (simple example):

DB_Name.show, Loads.Name, Locations.Name

Thas was called GCC or TOC, according to the version. As you see, the link between both tables is not mentioned.

I realise this is not common notation, and now I'm working for an SQL-Server based company, using basic SQL.
In order to show the same data, I need to write something like:

SELECT Loads.Name, Locations.Name FROM Loads, Locations
WHERE Loads.LocationId = Locations.Id
/* I know it's not a good idea to use a WHERE-clause for a join, /

/ but that's not the point here. */

As you can imagine, the tables are set up in such a way that the mentioned join is always applicable, so I'm wondering if there might be a way to configure my database in such a way that I don't always need to mention the WHERE (or JOIN clause), every time I write an SQL query.

Is this possible?
Thanks in advance