Re: possibilities for SQL optimization

On Thu, Apr 16, 2020 at 10:47 AM Michael Lewis <mlewis(at)entrata(dot)com> wrote:

> 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.
>

agreed but i wondered if partition elimination might be a faster way to
eliminate significant portions of table up fron then possibly parallelize
remaining partitioned brin index scans. not even sure its worth trying
though. this is a data volume vs cache size and predicate count (w/ each
predicate requiring a very efficient but not instantaneous index lookup)
issue.

>
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?
>

that's not really clear to me at this point but data will grow linearly for
a year and then remain constant. i think current volume represents ~ 3
months of data but i'm not sure. it is the 40-300ms response time that is
the issue. this system has ridiculous time constraints and has other
processing separate from database queries to account for. query response
times must fit into those requirements but since other pieces are still
being looked at, specific requirements aren't available as far as i
understand. "as fast as possible" is what we have right now. :(

>
>
> 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 */
>

I just tried that. results were same as without. thanks for the suggestion!