Re: Queries containing ORDER BY and LIMIT started to work slowly

[Rondat Flyag]

Hi and thank you for the response. I tried VACUUM ANALYZE for three tables, but without success. I also tried to set enable_seqscan=off and the query took even more time. If I set enable_sort=off then the query takes a lot of time and I cancel it. Please see the attached query plans. Cheers,Serg 29.08.2023, 23:11, "Jeff Janes" :On Tue, Aug 29, 2023 at 2:55 PM Rondat Flyag  wrote:I took the dump just to store it on another storage (external HDD). I didn't do anything with it. I don't see how that could cause the problem, it is probably just a coincidence.  Maybe taking the dump held a long-lived snapshot open which caused some bloat.   But if that was enough to push your system over the edge, it was probably too close to the edge to start with. Do you have a plan for the query while it was fast?  If not, maybe you can force it back to the old plan by setting enable_seqscan=off or perhaps enable_sort=off, to let you capture the old plan for comparison. The estimate for the seq scan of  isbns_statistics is off by almost a factor of 2.  A seq scan with no filters and which can not stop early should not be hard to estimate accurately, so this suggests autovac is not keeping up.  VACUUM ANALYZE all of the involved tables and see if that fixes things. Cheers, Jeff QUERY 
PLAN  
-
 Limit  (cost=766983.18..767070.52 rows=100 width=498) (actual 
time=5508.261..5508.532 rows=100 loops=1)
   Buffers: shared hit=30249 read=342473, temp read=16856 written=28392
   ->  Merge Join  (cost=766983.18..2008284.16 rows=1421289 width=498) (actual 
time=5508.260..5508.527 rows=100 loops=1)
 Merge Cond: ((books.asin)::text = (asins.value)::text)
 Buffers: shared hit=30249 read=342473, temp read=16856 written=28392
 ->  Index Scan using books_asin_key on books  (cost=0.43..1216522.35 
rows=1403453 width=333) (actual time=0.007..0.150 rows=100 loops=1)
   Buffers: shared hit=103
 ->  Materialize  (cost=766980.48..774092.68 rows=1422439 width=155) 
(actual time=5508.248..5508.304 rows=100 loops=1)
   Buffers: shared hit=30146 read=342473, temp read=16856 
written=28392
   ->  Sort  (cost=766980.48..770536.58 rows=1422439 width=155) 
(actual time=5508.245..5508.293 rows=100 loops=1)
 Sort Key: asins.value
 Sort Method: external merge  Disk: 136864kB
 Buffers: shared hit=30146 read=342473, temp read=16856 
written=28392
 ->  Hash Join  (cost=55734.25..509782.68 rows=1422439 
width=155) (actual time=412.394..2071.400 rows=1404582 loops=1)
   Hash Cond: (asins_statistics.asin_id = asins.id)
   Buffers: shared hit=30146 read=342473, temp 
read=11281 written=11279
   ->  Seq Scan on asins_statistics  
(cost=0.00..373686.39 rows=1422439 width=120) (actual time=0.005..782.893 
rows=1404582 loops=1)
 Buffers: shared hit=16989 read=342473
   ->  Hash  (cost=27202.89..27202.89 rows=1404589 
width=35) (actual time=412.025..412.026 rows=1404589 loops=1)
 Buckets: 1048576  Batches: 2  Memory Usage: 
51393kB
 Buffers: shared hit=13157, temp written=4363
 ->  Seq Scan on asins  (cost=0.00..27202.89 
rows=1404589 width=35) (actual time=0.010..151.754 rows=1404589 loops=1)
   Buffers: shared hit=13157
 Planning time: 0.770 ms
 Execution time: 5525.959 ms
(25 rows)



SET enable_seqscan = OFF;



QUERY PLAN  
  
--
 Limit  (cost=10001314403.06..10001314490.39 rows=100 width=498) (actual 
time=6171.243..6171.489 rows=100 loops=1)
   Buffers: shared hit=1030733 read=346050, temp read=22327 written=34044
   ->  Merge Join  (cost=10001314403.06..10002555704.04 rows=1421289 width=498) 
(actual time=6171.241..6171.478 rows=100 loops=1)
 Merge Cond: ((books.asin)::text = (asins.value)::text)
 Buffers: shared hit=1030733 read=346050, temp read=22327 written=34044
 ->  Index Scan using books_asin_key on books  (cost=0.43..1216522.35 
rows=1403453 width=333) (actual time=0.019..0.144 rows=100 loops=1)
   Buffers: shared hit=103
 ->  Materialize  (cost=10001314400.36..

Re: Queries containing ORDER BY and LIMIT started to work slowly

[Rondat Flyag]

Thanks for the response.Sure, I thought about it and even bought another drive. The current drive is SSD, as far as I'm concerned write operations degrade SSDs. Even so, why other queries work fine? Why the query joining two tables instead of three works fine? Cheers,Serg 30.08.2023, 00:07, "Rick Otten" :  On Tue, Aug 29, 2023 at 3:57 PM Rondat Flyag  wrote:I took the dump just to store it on another storage (external HDD). I didn't do anything with it. 29.08.2023, 21:42, "Jeff Janes" :  On Tue, Aug 29, 2023 at 1:47 PM Rondat Flyag  wrote:I have a legacy system that uses `Posgresql 9.6` and `Ubuntu 16.04`. Everything was fine several days ago even with standard Postgresql settings. I dumped a database with the compression option (maximum compression level -Z 9) in order to have a smaller size (`pg_dump --compress=9 database_name > database_name.sql`). After that I got a lot of problems. You describe taking a dump of the database, but don't describe doing anything with it.  Did you replace your system with one restored from that dump?  If so, did vacuum and analyze afterwards? Cheers, Jeff Since this is a very old system and backups are fairly I/O intensive, it is possible you have a disk going bad?  Sometimes after doing a bunch of I/O on an old disk, it will accelerate its decline.  You could be about to lose it altogether. 

Re: Queries containing ORDER BY and LIMIT started to work slowly

[David Rowley]

On Thu, 31 Aug 2023 at 06:32, Rondat Flyag  wrote:
> I tried VACUUM ANALYZE for three tables, but without success. I also tried to 
> set enable_seqscan=off and the query took even more time. If I set 
> enable_sort=off then the query takes a lot of time and I cancel it.
>
> Please see the attached query plans.

It's a little hard to comment here as I don't see what the plan was
before when you were happy with the performance. I also see the
queries you mentioned in the initial email don't match the plans.
There's no table called "isbns" in the query. I guess this is "asins"?

Likely you could get a faster plan if there was an index on
asins_statistics (asin_id).  That would allow a query plan that scans
the isbns_value_key index and performs a parameterised nested loop on
asins_statistics using the asins_statistics (asin_id) index.  Looking
at your schema, I don't see that index, so it's pretty hard to guess
why the plan used to be faster.  Even if the books/asins merge join
used to take place first, there'd have been no efficient way to join
to the asins_statistics table and preserve the Merge Join's order (I'm
assuming non-parameterized nested loops would be inefficient in this
case). Doing that would have also required the asins_statistics
(asin_id) index.  Are you sure that index wasn't dropped?

However, likely it's a waste of time to try to figure out what the
plan used to be. Better to focus on trying to make it faster. I
suggest you create the asins_statistics (asin_id) index. However, I
can't say with any level of confidence that the planner would opt to
use that index if it did exist.   Lowering random_page_cost or
increasing effective_cache_size would increase the chances of that.

David

Re: Index bloat and REINDEX/VACUUM optimization for partial index

[jayaprabhakar k]

On Tue, Aug 29, 2023, 12:43 PM Jeff Janes  wrote:

> On Mon, Aug 28, 2023 at 8:33 PM jayaprabhakar k 
> wrote:
>
>> Hi,
>>
>> TL;DR:
>> Observations:
>>
>>1. REINDEX requires a full table scan
>>   - Roughly create a new index, rename index, drop old index.
>>   - REINDEX is not incremental. running reindex frequently does not
>>   reduce the future reindex time.
>>2. REINDEX does not use the index itself
>>3. VACUUM does not clean up the indices. (relpages >> reltuples) I
>>understand, vacuum is supposed to remove pages only if there are no live
>>tuples in the page, but somehow, even immediately after vacuum, I see
>>relpages significantly greater than reltuples. I would have assumed,
>>relpages <= reltuples
>>4. Query Planner does not consider index bloat, so uses highly
>>bloated partial index that is terribly slow over other index
>>
>> Your points 3 and 4 are not correct.  empty index pages are put on a
> freelist for future reuse, they are not physically removed from the
> underlying index files.  Maybe they are not actually getting put on the
> freelist or not being reused from the freelist for some reason, but that
> would be a different issue.  Use the extension pgstattuple to see what its
> function pgstatindex says about the index.
>
> The planner does take index bloat into consideration, but its effect size
> is low.  Which it should be, as empty or irrelevant pages should be
> efficiently skipped during the course of most index operations. To figure
> out what is going with your queries, you should do an EXPLAIN (ANALYZE,
> BUFFERS) of them, but with it being slow and with it being fast.
>
>
>> Question: Is there a way to optimize postgres vacuum/reindex when using
>> partial indexes?
>>
>
> Without knowing what is actually going wrong, I can only offer
> generalities.  Make sure you don't have long-lived transactions which
> prevent efficient clean up.  Increase the frequency on which vacuum runs on
> the table.  It can't reduce the size of an already bloated index, but by
> keeping the freelist stocked it should be able prevent it from getting
> bloated in the first place.  Also, it can remove empty pages from being
> linked into the index tree structure, which means they won't need to be
> scanned even though they are still in the file.  It can also free up space
> inside non-empty pages for future reuse within that same page, and so that
> index tuples don't need to be chased down in the table only to be found to
> be not visible.
>
>
>> ```
>> SELECT [columns list]
>>   FROM tasks
>>   WHERE status NOT IN (3,4,5) AND created > NOW() - INTERVAL '30 days'
>> AND updated < NOW() - interval '30 minutes'
>> ```
>>
>> Since we are only interested in the pending tasks, I created a partial
>> index
>>  `*"tasks_pending_status_created_type_idx" btree (status, created,
>> task_type) WHERE status <> ALL (ARRAY[3, 4, 5])*`.
>>
>
> This looks like a poorly designed index.  Since the status condition
> exactly matches the index where clause, there is no residual point in
> having "status" be the first column in the index, it can only get in the
> way (for this particular query).  Move it to the end, or remove it
> altogether.
>
Interesting. I don't understand why it will get in the way. Unfortunately
we have a few other cases where status is used in filter. That said, I will
consider how to get this to work.
Would removing status from the index column, improve HOT updates %? For
example, changing status from 1->2, doesn't change anything on the index
(assuming other criteria for HOT updates are met), but I am not sure how
the implementation is.


> Within the tuples which pass the status check, which inequality is more
> selective, the "created" one or "updated" one?
>
Obviously updated time is more selective (after status), and the created
time is included only to exclude some bugs in our system that had left some
old tasks stuck in progress (and for sorting). We do try to clean
up occasionally, but not each time.
However we cannot add an index on `updated` column because that timestamp
gets updated over 10x on average for each task. Since if a single index use
a column, then the update will not be HOT, and every index needs to be
updated. That will clearly add a bloat to every index. Did I miss something?


>
> Cheers,
>
> Jeff
>