Re: Slow query on a one-tuple table
Luís Roberto Weck <[email protected]>
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <[email protected]> |
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_index