Re: Slow query on a one-tuple table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Em 19/09/2019 17:41, Luís Roberto Weck escreveu:
Em 19/09/2019 17:24, Luís Roberto Weck escreveu:
Em 19/09/2019 17:11, Igor Neyman escreveu:
With LIMIT 1, I get 3 shared buffers hit, pretty much always.

____________________________________________________________________________________

Check if assessoria_pkey index is bloated.

Regards,
Igor Neyman



With this query[1] it shows:

current_database|schemaname|tblname   |idxname |real_size|extra_size|extra_ratio|fillfactor|bloat_size|bloat_ratio|is_na| ----------------|----------|----------|---------------|---------|----------|-----------|----------|----------|-----------|-----| database_name   |public    |assessoria|assessoria_pkey| 16384|         0|        0.0|        90|       0.0| 0.0|false|

[1]https://github.com/ioguix/pgsql-bloat-estimation/blob/master/btree/btree_bloat-superuser.sql



Using the quer provided here[1] I see this comment:

  /*
   * distinct_real_item_keys is how many distinct "data" fields on page
   * (excludes highkey).
   *
   * If this is less than distinct_block_pointers on an internal page, that    * means that there are so many duplicates in its children that there are    * duplicate high keys in children, so the index is probably pretty bloated.
   *
   * Even unique indexes can have duplicates.  It's sometimes interesting to    * watch out for how many distinct real items there are within leaf pages,    * compared to the number of live items, or total number of items.  Ideally,
   * these will all be exactly the same for unique indexes.
   */

In my case, I'm seeing:

distinct_real_item_keys|distinct_block_pointers|
-----------------------|-----------------------|
                      1|                     63|

This is about half an hour after running VACUUM FULL ANALYZE on the table.

What can I do to reduce this?


[1] https://wiki.postgresql.org/wiki/Index_Maintenance#Summarize_keyspace_of_a_B-Tree_inde
Like Igor suggested, the index bloat seems to be at fault here. After dropping the PK, I'm getting these plans:

First run (SELECT asscod, asscambol FROM ASSESSORIA WHERE asscod = 1 ORDER BY asscod):

 Seq Scan on public.assessoria  (cost=0.00..88.01 rows=1 width=62) (actual time=0.242..0.810 rows=1 loops=1)
   Output: asscod, asscambol
   Filter: (assessoria.asscod = 1)
   Buffers: shared hit=88
 Planning Time: 0.312 ms
 Execution Time: 0.876 ms
(6 rows)

Subsequent runs get increasingly faster, up to 0.080ms execution times.

Using LIMIT 1, I get on the first run:

 Limit  (cost=0.00..88.01 rows=1 width=62) (actual time=0.252..0.254 rows=1 loops=1)
   Output: asscod, asscambol
   Buffers: shared hit=17
   ->  Seq Scan on public.assessoria  (cost=0.00..88.01 rows=1 width=62) (actual time=0.250..0.250 rows=1 loops=1)
         Output: asscod, asscambol
         Filter: (assessoria.asscod = 1)
         Buffers: shared hit=17
 Planning Time: 0.334 ms
 Execution Time: 0.296 ms


Subsequent runs look more like this:

 Limit  (cost=0.00..88.01 rows=1 width=62) (actual time=0.057..0.057 rows=1 loops=1)
   Output: asscod, asscambol
   Buffers: shared hit=17
   ->  Seq Scan on public.assessoria  (cost=0.00..88.01 rows=1 width=62) (actual time=0.056..0.056 rows=1 loops=1)
         Output: asscod, asscambol
         Filter: (assessoria.asscod = 1)
         Buffers: shared hit=17
 Planning Time: 0.082 ms
 Execution Time: 0.068 ms

I have about 6 bigint fields in this table that are very frequently updated, but none of these are indexed. I thought that by not having an index on them, would make all updates HOT, therefore not bloating the primary key index. Seems I was wrong?





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux