1

I run an app with a database of PostgreSQL 10.10

I am optimizing a bunch of queries in our application, with great results so far, but there is one specific query that takes about 1 minute if not served from the PG shared buffers, and I don't really know how to drastically optimize it.

The query selects about 100,000 rows out of a table holding about 35M rows in total, meeting criteria for which index exists, then groups them by MONTH of a datetime field. The query is generated by ankane's groupdate ruby gem and looks like this:

SELECT
  COUNT(*) AS count_all,
  (
    DATE_TRUNC(
      'month',
      ("ahoy_events"."time" :: timestamptz) AT TIME ZONE 'America/Chicago'
    )
  ) AT TIME ZONE 'America/Chicago' AS date_trunc_month_ahoy_events_time_timestamptz_at_time_zone_amer
FROM "ahoy_events"
WHERE
  "ahoy_events"."merchant_id" = 5081
  AND "ahoy_events"."name" = 3
  AND "ahoy_events"."time" > '2019-01-15 21:31:54.794496'
  AND ("ahoy_events"."time" IS NOT NULL)
GROUP BY
  (
    DATE_TRUNC(
      'month',
      ("ahoy_events"."time" :: timestamptz) AT TIME ZONE 'America/Chicago'
    )
  ) AT TIME ZONE 'America/Chicago';

The table itself looks like this:

CREATE TABLE public.ahoy_events (
    id bigint NOT NULL,
    visit_id integer,
    person_id integer,
    name integer NOT NULL,
    properties jsonb,
    "time" timestamp without time zone,
    deprecated_semantic_type character varying,
    semantic_type_id bigint,
    product_id bigint,
    merchant_id bigint
);
ALTER TABLE ONLY public.ahoy_events ADD CONSTRAINT ahoy_events_pkey PRIMARY KEY (id);
ALTER TABLE ONLY public.ahoy_events ADD CONSTRAINT fk_rails_33ad087eb5 FOREIGN KEY (product_id) REFERENCES public.products(id);
ALTER TABLE ONLY public.ahoy_events ADD CONSTRAINT fk_rails_de0839b608 FOREIGN KEY (semantic_type_id) REFERENCES public.semantic_types(id);

CREATE INDEX index_ahoy_events_on_merchant_id ON public.ahoy_events USING btree (merchant_id);
CREATE INDEX index_ahoy_events_on_merchant_id_and_time ON public.ahoy_events USING btree (merchant_id, "time");
CREATE INDEX index_ahoy_events_on_merchant_id_and_name_and_time ON public.ahoy_events USING btree (merchant_id, name, "time");

CREATE INDEX index_ahoy_events_on_person_id ON public.ahoy_events USING btree (person_id);
CREATE INDEX index_ahoy_events_on_person_id_and_name ON public.ahoy_events USING btree (person_id, name);

CREATE INDEX index_ahoy_events_on_product_id_and_name ON public.ahoy_events USING btree (product_id, name);

CREATE INDEX index_ahoy_events_on_semantic_type_id ON public.ahoy_events USING btree (semantic_type_id);

CREATE INDEX index_ahoy_events_on_url ON public.ahoy_events USING gin (((properties ->> 'url'::text)) public.gin_trgm_ops);

CREATE INDEX index_ahoy_events_on_visit_id ON public.ahoy_events USING btree (visit_id);
CREATE INDEX index_ahoy_events_on_visit_id_and_name ON public.ahoy_events USING btree (visit_id, name);

When I EXPLAIN this query, this is what I get:

 HashAggregate  (cost=174126.69..174652.97 rows=105256 width=16) (actual time=46752.289..46752.814 rows=13 loops=1)
   Output: count(*), (timezone('America/Chicago'::text, date_trunc('month'::text, timezone('America/Chicago'::text, ("time")::timestamp with time zone))))
   Group Key: timezone('America/Chicago'::text, date_trunc('month'::text, timezone('America/Chicago'::text, (ahoy_events."time")::timestamp with time zone)))
   Buffers: shared hit=2729 read=86382
   I/O Timings: read=44671.925
   ->  Bitmap Heap Scan on public.ahoy_events  (cost=1365.14..174021.43 rows=105256 width=8) (actual time=80.374..46656.883 rows=98175 loops=1)
         Output: timezone('America/Chicago'::text, date_trunc('month'::text, timezone('America/Chicago'::text, ("time")::timestamp with time zone)))
         Recheck Cond: ((ahoy_events.merchant_id = 1923) AND (ahoy_events.name = 3) AND (ahoy_events."time" > '2019-01-15 21:31:54.794496'::timestamp without time zone) AND (ahoy_events."time" IS NOT NULL))
         Heap Blocks: exact=88624
         Buffers: shared hit=2729 read=86382
         I/O Timings: read=44671.925
         ->  Bitmap Index Scan on index_ahoy_events_on_merchant_id_and_name_and_time  (cost=0.00..1359.88 rows=105256 width=0) (actual time=62.504..62.504 rows=98175 loops=1)
               Index Cond: ((ahoy_events.merchant_id = 1923) AND (ahoy_events.name = 3) AND (ahoy_events."time" > '2019-01-15 21:31:54.794496'::timestamp without time zone) AND (ahoy_events."time" IS NOT NULL))
               Buffers: shared hit=1 read=486
               I/O Timings: read=39.374
 Planning time: 0.217 ms
 Execution time: 46755.851 ms

Now, if I do it immediately afterwards again, it executes in less than 300 msec, with the buffer share hit as only difference. It seems that the IO is the bottleneck of the Bitmap Heap Scan here, but I'm a bit lost for what I can do about it.

Any ideas?

EDIT: A few things worth mentioning:

  • Throughout the entire table, there are (currently) about 5000 different merchant ID's associated with this table
  • The name field holds a required value between 0 and 7
  • The America/Chicago time zone is added procedurally, and changes merchant-by-merchant.

EDIT 2: Why can't this query do an INDEX ONLY scan? I would expect all the necessary data for this query to already be part of the index.

  • It probably can use an index only scan. (In my hands, it does. But my table is empty.) It just thinks it will be slower, so it decides not to. What happens if you set enable_bitmapscan=off? Is your table freshly VACUUMed? – jjanes Jan 16 at 20:28
  • No, it wasn't. sigh, and the last autovacuum was older than this index. I vacuum'd it, and now it does an INDEX ONLY scan, queries are now in between 0.5-1 sec, which will be acceptable (for now). I still don't understand how bad it could have been for the planner to think that a BITMAP operation was going to be faster though. – The Pellmeister Jan 17 at 15:25
  • 1
    The only advantage of IOS is it will avoid visiting the table. If it can't avoid that because few pages are marked 'all visible', then an attempted IOS will be worse than bitmap. They both need to visit the same pages in the table, but bitmap visits them in order, and only once for each page. The unproductive IOS would visit them in random order, and possibly several times each. – jjanes Jan 17 at 16:23
2

The query simply has to read the 98175 rows which are located in almost as many 8KB-blocks, so there is no way around reading all these blocks, and if they are not cached, that is going to be slow.

Your only chance to make that faster is to make sure that the rows are lumped together in fewer blocks.

That could be done by rewriting the table like this:

CLUSTER ahoy_events USING index_ahoy_events_on_merchant_id_and_name_and_time;

This operation might take a longer time, during which the table is inaccessible, so you need down time.

Also, this ordering degrades over time, so you have to schedule such CLUSTER statements every now and then.

Your alternatives are more RAM, so that the whole table can be cached with pg_prewarm, or faster storage so that the reads are faster. But your I/O speed already seems decent.

  • 1
    CLUSTER unfortunately is no option as we can't afford downtime on this table. I do need to appreciate your answer though since it had me focus on IO time, and helped spark my thought of it potentially being an INDEX ONLY query, which would solve it. – The Pellmeister Jan 17 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.