Re: Improving Performance of Query ~ Filter by A, Sort by B - Mailing list pgsql-performance

On Tue, Jul 10, 2018 at 11:07 AM, Lincoln Swaine-Moore <lswainemoore@gmail.com> wrote:

Something about the estimated row counts (this problem persisted after I tried ANALYZEing)

forces usage of the tmstmp index and Merge Append (which seems wise) but also a filter condition on parent_id over an index condition, which is apparently prohibitively slow.

I tried creating a multicolumn index like:

CREATE INDEX "a_partition1_parent_and_tmstmp_idx" on "a_partition2" USING btree ("parent_id", "tmstmp" DESC);

But this didn't help (it wasn't used).

...

This query plan (which is the same as when LIMIT is removed) has been a good short term solution when the number of "parent_id"s I'm using is still relatively small, but unfortunately queries grow untenably slow as the number of "parent_id"s involved increases:

I'd be very grateful for help with one or both of these questions:

1) Why is adding an unnecessary (from the perspective of result correctness) ORDER BY valuable for forcing the parent_id index usage, and does that indicate that there is something wrong with my table/indexes/statistics/etc.?

It finds the indexes on tmstmp to be falsely attractive, as it can walk in tmstmp order and so avoid the sort. (Really not the sort itself, but the fact that sort has to first read every row to be sorted, while walking an index can abort once the LIMIT is satisfied).  Adding an extra phrase to the ORDER BY means the index is no longer capable of delivering rows in the needed order, so it no longer looks falsely attractive.  The same thing could be obtained by doing a dummy operation, such as ORDER BY tmstmp + '0 seconds' DESC.  I prefer that method, as it is more obviously a tuning trick.  Adding in "id" looks more like a legitimate desire to break any ties that might occasionally occur in tmstmp.

As Tom pointed out, there clearly is something wrong with your statistics, although we don't know what is causing it to go wrong.  Fixing the statistics isn't guaranteed to fix the problem, but it would be a good start.

 

2) Is there any way I can improve my query time when there are many "parent_id"s involved? I seem to only be able to get the query plan to use at most one of the parent_id index and the tmstmp index at a time. Perhaps the correct multicolumn index would help?