1

I have a query(Posgres) that looks like this (I replaced fields with * meaning that fields could vary):

SELECT *
FROM actions
WHERE
(logs."group"=$1 OR logs."team" IN ($2))
and actions.datetime::date=(timezone('UTC', now()) + coalesce(actions.utc_offset, 0) * INTERVAL '1 second')::date
LIMIT $3;

I cannot change the query, so I need to find another way to optimize the query on big amount of data around 10mil rows

I tried creating index separately for "group" and for "team", also for both of them in different order. But "WHERE .. OR .." does not let me use index in my queries.

Is it possible to create index for such case without changing the query?

Here is the DDL for the table with indexes that I tried:

CREATE TABLE public.actions (
    team uuid NULL,
    domain int8 NULL,
    utc_offset int4 NULL,
    datetime timestamp NULL
);
CREATE INDEX idx_actions_coales ON public.actions USING btree (((datetime)::date), COALESCE(utc_offset, 0));
CREATE INDEX idx_actions_date ON public.actions USING btree (((datetime)::date));
CREATE INDEX idx_actions_date_0 ON public.actions USING btree (datetime);
CREATE INDEX idx_actions_date_desc ON public.actions USING btree (((datetime)::date) DESC);
CREATE INDEX idx_actions_date_newerdates ON public.actions USING btree (datetime) WHERE ((datetime)::date > '2023-04-01'::date);
CREATE INDEX idx_actions_date_utc_offset ON public.actions USING btree (((datetime)::date), utc_offset);
CREATE INDEX idx_actions_date_utc_offset__eq ON public.actions USING btree (((datetime)::date), COALESCE(utc_offset, 0));
CREATE INDEX idx_actions_date_utc_offset_subtract ON public.actions USING btree (((datetime)::date), utc_offset);
CREATE INDEX idx_actions_date_utc_offset_subtract_1 ON public.actions USING btree (((datetime)::date), utc_offset) WHERE ((datetime)::date = ((timezone('UTC'::text, '2023-06-01 00:00:00+03'::timestamp with time zone) + ((COALESCE(utc_offset, 0))::double precision * '00:00:01'::interval)))::date);
CREATE INDEX idx_actions_date_utc_offset_subtract_2 ON public.actions USING btree (((datetime)::date), utc_offset) WHERE ((datetime)::date > ((timezone('UTC'::text, '2023-06-01 00:00:00+03'::timestamp with time zone) + ((COALESCE(utc_offset, 0))::double precision * '00:00:01'::interval)))::date);
CREATE INDEX idx_actions_detector_type ON public.actions USING btree (detector_type);
CREATE INDEX idx_actions_team_id ON public.actions USING btree (team__id);
CREATE INDEX idx_actions_utc_offset_date ON public.actions USING btree (utc_offset, ((datetime)::date));
CREATE INDEX testindextesttest ON public.actions USING btree (detector_type) WHERE ((time_utc)::date > '2021-01-01'::date);

but all this won't work when I use OR in where

developer
  • 19
  • 3
  • `"WHERE .. OR .." does not let me use index in my queries.` - don't believe you. Show EXPLAIN ANALYSE output please along with counts of the numbers of rows involved for each condition. Also - you have a LIMIT without an ORDER BY - that isn't usually what you want. – Richard Huxton Jun 20 '23 at 10:01
  • The `OR` is not the big problem here, but the condition on `datetime` and `utc_offset`. You won't be able to make this query efficient unless you rewrite it. – Laurenz Albe Jun 20 '23 at 10:42
  • i just tried to use WHERE .. OR .. on random data in these fields, then index is used, but when i use on real life data, then index is not used. Does it mean that not using index more efficient and I can't optimize any better? – developer Jun 20 '23 at 11:33
  • The CREATE statement you show is not for the table used in your query. – jjanes Jun 20 '23 at 13:53
  • Your WHEREreferences two tables, but your FROM only has one table in it. – jjanes Jun 20 '23 at 13:58
  • yes you are right table names are wrong I tried this issue in different tables, but overall I understand that the condition on datetime and utc_offset cannot be indexed isn't it? – developer Jun 20 '23 at 17:16

0 Answers0