Thread: Slow query, possibly not using index

> If I try to select a single unused block this way:
> explain analyze select id from media.block b where nrefs =0 limit 1
> then it runs for more than 10 minutes (I'm not sure how long, I cancelled
> the query after 10 minutes).

Are you sure it isn't blocked on a lock?

Another theory is that the index contains many thousands of references
to now-dead rows, and the query is vainly searching for a live entry.
Given that EXPLAIN thinks there are only about 2300 live entries,
and yet you say the index is 400MB, this seems pretty plausible.

Have you disabled autovacuum, or something like that?  (REINDEX
could help here, at least till the index gets bloated again.)

You might think that even so, it shouldn't take that long ... but
indexes on UUID columns are a well known performance antipattern.
The index entry order is likely to have precisely zip to do with
the table's physical order, resulting in exceedingly-random access
to the table, which'll be horribly expensive when the table is so
much bigger than RAM.  Can you replace the UUID column with a simple
serial (identity) column?

> I believe it is not actually using the index, because reading a single
> (random?) entry from an index should not run for >10 minutes.

You should believe what EXPLAIN tells you about the plan shape.
(Its rowcount estimates are only estimates, though.)

> I'm aware of the problems with random UUID values. I was using this
> function to create ulids from the beginning:

Oh, well that would have been useful information to provide at the
outset. 

Now that we know the index order is correlated with creation
time, I wonder if it is also correlated with nrefs, in such a way that
scanning in index order is disastrous because all the desired rows are
at the end of the index.

Also, you deny deleting any rows, but that wasn't the point.  Do you
ever update nrefs from zero to nonzero?  That'd also leave dead
entries behind in this index.  If that is a routine event that is
correlated with creation time, it gets easier to believe that your
index could have lots of dead entries at the front.

We'd still have to figure out why autovacuum is failing to clean out
those entries in a timely fashion, but that seems like a plausible
way for the performance problem to exist.

> I can try to do VACUUM on this table, but I'm limited on resources. I think
> it will take days to do this. Maybe I can try to dump the whole database
> and restore it on another machine.

Pretty hard to believe that dump-and-restore would be faster than
VACUUM.

> (Is there a way to check the number of dead rows?)

I think contrib/pgstattuple might help.

 

All right, I started pgstattuple()  and I'll also do pgstatindex(), but it takes a while. I'll get back with the results.

=# select * from pgstatindex('media.idx_block_unused');
 version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
       4 |          2 |  389677056 |           546 |            114 |      23069 |           0 |         24384 |            90.03 |                  0
(1 row)

=# select * from pgstatindex('media.idx_block_unused');
 version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
       4 |          2 |  389677056 |           546 |            114 |      23069 |           0 |         24384 |            90.03 |                  0
(1 row)

After reindex:

=# select * from pgstatindex('media.idx_block_unused');
 version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
       4 |          0 |       8192 |             0 |              0 |          0 |           0 |             0 |              NaN |                NaN
(1 row)

 explain analyze select id from media.block b where nrefs =0 limit 1

QUERY PLAN                                                                                                                               |
-----------------------------------------------------------------------------------------------------------------------------------------+
Limit  (cost=0.14..0.46 rows=1 width=16) (actual time=0.010..0.011 rows=0 loops=1)                                                       |
  ->  Index Only Scan using idx_block_unused on block b  (cost=0.14..698.91 rows=2231 width=16) (actual time=0.008..0.009 rows=0 loops=1)|
        Heap Fetches: 0                                                                                                                  |
Planning Time: 0.174 ms                                                                                                                  |
Execution Time: 0.030 ms                                                                                                                 |

It is actually empty.

Now I only need to figure out why autovacuum did not work on the index.

Thank you

    Laszlo

> More important question is, how can I find out why the index was not auto vacuumed.

You should have a look at pg_stat_user_tables.  It'll let you know if
the table is being autovacuumed and how often.  If you're concerned
about autovacuum not running properly, then you might want to lower
log_autovacuum_min_duration.  Generally, anything that takes a
conflicting lock will cause autovacuum to cancel so that the
conflicting locker can get through.  Things like ALTER TABLE or even
an ANALYZE running will cancel most autovacuum runs on tables.

Also, this is a fairly large table and you do have the standard
autovacuum settings.  Going by pgstattuple, the table has 39652836
tuples.  Autovacuum will trigger when the statistics indicate that 20%
of tuples are dead, which is about 8 million tuples. Perhaps that's
enough for the index scan to have to skip over a large enough number
of dead tuples to make it slow. You might want to consider lowering
the autovacuum scale factor for this table.

Also, ensure you're not doing anything like calling pg_stat_reset();

It might be worth showing us the output of:

select * from pg_stat_user_tables where relid = 'media.block'::regclass;