1

We have the following table which contains 25Mi rows and growing in production:

create table "Common"."Event"
(
    "Id" bigserial not null
        constraint "PK_Event"
            primary key,
    "AggregateId" citext not null,
    "AggregateType" integer not null,
    "CreatedBy" citext not null,
    "CreatedUtc" timestamp not null,
    "Data" jsonb,
    "EventId" uuid not null,
    "PropertyId" citext,
    "Type" citext not null,
    "UpdatedBy" citext not null,
    "UpdatedUtc" timestamp not null,
    "AccountCode" text not null,
    "TrackingId" citext not null,
    "OriginClientId" citext not null,
    "OriginSubjectId" citext
);

create index "IX_Event_AccountCode_AggregateType_Type_PropertyId_TrackingId"
    on "Common"."Event" ("AccountCode", "AggregateType", "Type", "PropertyId", "TrackingId");

create index "IX_Event_AccountCode_AggregateType_Type_PropertyId_AggregateId"
    on "Common"."Event" ("AccountCode", "AggregateType", "Type", "PropertyId", "AggregateId");

Executing the following query, takes time:

SELECT e."Id", e."AggregateId", e."CreatedUtc", e."OriginClientId", e."OriginSubjectId", e."PropertyId", e."Type"
FROM "Common"."Event" AS e
WHERE e."AccountCode" = 'XXXX'
  AND e."AggregateType" = 1
  AND e."Type" IN ('type-1', 'type-2', 'type-3', 'type-4')
  AND e."PropertyId" = 'YYYY'
ORDER BY e."Id" DESC
LIMIT 100 OFFSET 0;

And produces the following execution plan:

enter image description here

The query in some cases it's much slower than this. Executing the query again is very fast, also when changing the offset.

Looking at the Index scan output:

enter image description here

we noticed that we could benefit from having a covering index in order to let postgres scan only the index. Then we created the following covering index:

create index "IX_Event_Test_Covering_Index"
    on "Common"."Event" ("AccountCode", "AggregateType", "Type", "PropertyId", "AggregateId", "OriginClientId", "OriginSubjectId", "CreatedUtc", "Id");

Which produces the following execution plan:

enter image description here

Which is pretty cool.

We also run VACUUM ANALYZE and also tried to create statistics for multiple columns to let postgres better estimate the number of rows but nothing helped apart from the covering index.

So, now the question would be if you guys think this would be the correct approach to tackle this issue or if we are missing anything and there is a better solution.

I'm not sure that the amount of data we have justifies already partitioning the table.

4
  • 1
    When optimizing, you need a full picture because optimizing that query can make all the other queries smaller which might not be what you're trying to achieve... Could you please share your version of Postgres? What is the size of your table? How much shared_buffers is allocated? How much RAM do you have? Are temp files created by that query? – Arkhena Mar 19 at 13:09
  • We are running postgres 12.4 in AWS RDS on an instance with 8 vCPUs and 32GB of RAM. The table has 25Mi rows. The command SELECT pg_size_pretty(pg_total_relation_size('"Common"."Event"')); returns 36GB. Shared buffers is 8015016kB. It seems no temp files are created. – dario Mar 19 at 14:24
  • Your estimates seem to be off by large factors. Could you run analyze "Common"."Event" and look at the explain plan again? – Arkhena Mar 19 at 15:02
  • As I stated in the question, we did already run VACUUM ANALYZE on that table without any differences. We also tried creating statistics for multiple columns, which improved the estimations but didn't improve the execution time. – dario Mar 19 at 15:26
2

A index-only scan is faster than anything I can recommend, particularly after vacuuming the table.

But it seems that this large index that contains more than half of the columns of the table is probably a bit over the top.

If you want to do cheaper, try a B-tree index like

CREATE INDEX ON "Common"."Event" ("AccountCode", "AggregateType", "PropertyId", "Type");

If any of these conditions are not selective (that is, the condition does not remove many rows), omit it from the index.

It is important that "Type" is last in the index column list.

2
  • I'm also concerned about the new index we created, that's why I decided to ask here. I will try your suggestion. – dario Mar 19 at 17:51
  • I would also recommend monitoring other statements and particularly writes on this table to be sure not to make things worse for other queries... – Arkhena Mar 20 at 8:51
1

I don't think there is anything wrong with the index-only scan approach, but you may have to tweak your autovacuuming settings to ensure the table remains mostly visible at all times. Are any of the column you would need to include in it updated far more frequently than the other columns are? If so, including that could be a problem.

Another possibility is to take advantage of the LIMIT by reading the index in order, then stopping once you accumulate 100 rows which meet all the conditions. But the IN-list interferes with reading an index in order, so you (or the planner, if you have both indexes in place at the same time) would have to decide which is more important to get directly from the index, the "Type" IN-list restriction or the ORDER BY "Id". So, how selective is your "Type" In-list restriction? How restrictive does the planner think it is?

That would mean an index on

("AccountCode","AggregateType","PropertyId","Id")

Plus you can tack on the end of that any other columns, like enough to do an index-only scan, or just "Type" so that those rows can be eliminated in the index without visiting the table.

Where do you get your IN-list from? If it is set in stone for all queries of this type, then you could build a partial index with that list in the index WHERE clause (which means it would no longer interfere with efficient ordering). If it is user supplied (like from a list of check-boxes on a web page), that wouldn't work.

There are other more advanced options as well, but they are would require writing the query in contorted ways which are hard to understand and hard to correctly modify, and probably would not be worth it.

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.