From: | Michael Lewis <mlewis(at)entrata(dot)com> |
---|---|
To: | Olivier Gautherot <ogautherot(at)gautherot(dot)net> |
Cc: | Chris Stephens <cstephens16(at)gmail(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org >> PG-General Mailing List" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: possibilities for SQL optimization |
Date: | 2020-04-16 15:46:35 |
Message-ID: | CAHOFxGoNMVSuHs=VFkHUw5bDwkX12k30dYCy8d3WnFwxALpU_A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
> My other thought was to range partition by pixelID + brin index.
>>>>
>>>
I would expect brin index to be INSTEAD of partitioning. You didn't share
buffer hits, which I expect were 100% on the subsequent explain analyze
runs, but the index scan may still be faster if the planner knows it only
needs to scan a few small indexes on one, or a few, partitions.
What sort of growth do you see on this table? Is future scalability a
significant concern, or is the problem just that 40-300ms for this select
is unacceptable?
https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-ASYNC-BEHAVIOR
Have you tuned effective_io_concurrency? The documentation says "this
setting only affects bitmap heap scans" and nearly all the time is there.
If it is still set to 1 as default, then increasing to 200 or perhaps more
might be prudent when on SSD or other memory backed storage. You don't even
need to change the server config defaults for testing the impact-
set effective_io_concurrency = 200;
/* select query */
reset effective_io_concurrency; /* if doing other things in the same
session and wanting to revert to default behavior, else just disconnect */
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2020-04-16 17:36:22 | Re: timestamp and timestamptz |
Previous Message | Olivier Gautherot | 2020-04-16 15:20:48 | Re: possibilities for SQL optimization |