Why are my COMMITs after SELECT-only transactions slow with PostgreSQL?

hop of those help? Your query is doing a full table scan on the larger table. An obvious speed up is to add an index on event_track(inboundid, eventid). Postgres should be able to use the index on your query as written. You can rewrite the query as:

SELECT te.eventid
FROM track_event te join
     temp_message tm
     on te.inboundid  = tm.messageid;

select (select eventid from track_event te WHERE tm.messageid = te.inboundid) as eventid
from temp_message tm;

select eventid
from (select (select eventid from track_event te WHERE tm.messageid = te.inboundid) as eventid
      from temp_message tm
     ) tm
where eventid is not null;

I wish this help you I used psql instead of Postico and my issue was resolved, seems like Postico doesn't support this functionality!

around this issue I am using PostgreSQL 9.5. I have a table which is almost 20GB's. It has a primary key on the ID column which is an auto-increment column, however I am running my queries on another column which is a timestamp... I am trying to select/update/delete on the basis of a timestamp column but the queries are very slow. For example: A select on this table `where timestamp_column::date (current_date - INTERVAL '10 DAY')::date) is taking more than 15 mins or so.. Can you please help on what kind of Index should I add to this table (if needed) to make it perform faster? , You can create an index with your clause expression:

CREATE INDEX ns_event_last_updated_idx ON ns_event (CAST(last_updated AT TIME ZONE 'UTC' AS DATE));

select * from ns_event where Last_Updated < (current_date - INTERVAL '25 DAY');

To fix this issue I am looking for an idea to optimize my query. , The ideal index for this query would be:

CREATE INDEX ON customers_material_events (reference, date);

it helps some times Don't apply date functions on the timestamp column: this requires repeated computation for each row (5 total), and prevents the database from taking advantage of an existing index on the timestamp column:
This should be faster:

select count(*)
from temperatures
where 
    timestamp >= '2020-02-02 00:00:00'::timestamp 
    and timestamp < '2020-02-01 00:01:00'::timestamp