tag:blogger.com,1999:blog-7476449567742726187.post7909169834652469560..comments2023-12-18T12:25:52.296-08:00Comments on Database Soup: New Finding Unused Indexes QueryJosh Berkushttp://www.blogger.com/profile/09671139717468724246noreply@blogger.comBlogger10125tag:blogger.com,1999:blog-7476449567742726187.post-78495060166194682922014-10-14T10:02:29.112-07:002014-10-14T10:02:29.112-07:00Haven't tested that. Patches welcome!Haven't tested that. Patches welcome!Josh Berkushttps://www.blogger.com/profile/09671139717468724246noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-85762280207015743872014-10-14T02:46:17.890-07:002014-10-14T02:46:17.890-07:00Looks like indexes on materilized views are ignore...Looks like indexes on materilized views are ignored?!Anonymoushttps://www.blogger.com/profile/12332735622909307018noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-9328754049273437722014-05-05T05:10:37.921-07:002014-05-05T05:10:37.921-07:00Right, I have several large tables in the DB in qu...Right, I have several large tables in the DB in question that only receive updates at most once per quarter, do it's entirely probable that they will rarely see updates during the stat period. so yes adding the check is probable the right thing.Anonymoushttps://www.blogger.com/profile/04535538708723066992noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-55952071084606347172014-05-04T14:27:23.062-07:002014-05-04T14:27:23.062-07:00Josh, I was getting the divide by zero error when ...Josh, I was getting the divide by zero error when running this on a streaming standby machine.joevandykhttps://www.blogger.com/profile/17835717350954324810noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-37501424482981691432014-05-03T16:20:51.805-07:002014-05-03T16:20:51.805-07:00Darcy, I'd thought about that, but then I thou...Darcy, I'd thought about that, but then I thought: why would total_writes ever be 0? That means that there's been zero non-HOT updates during the stats period. <br /><br />Probably better to error-check for it though.Josh Berkushttps://www.blogger.com/profile/09671139717468724246noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-44298451996911119282014-05-03T08:43:37.656-07:002014-05-03T08:43:37.656-07:00it was simple..
SELECT 'High-Write Large Non-...it was simple..<br /><br />SELECT 'High-Write Large Non-Btree' as reason, index_ratios.*, 4 as grp<br />FROM index_ratios, all_writes<br />WHERE<br /> ( case when total_writes = 0 THEN 0 ELSE (writes::NUMERIC / total_writes) END ) > 0.02<br /> AND NOT idx_is_btree<br /> AND index_bytes > 100000000<br />ORDER BY grp, index_bytes DESC )Anonymoushttps://www.blogger.com/profile/04535538708723066992noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-16189398266546182872014-05-03T08:38:54.529-07:002014-05-03T08:38:54.529-07:00There should probably be some sanity checking in t...There should probably be some sanity checking in the math, as when I run this query on a couple of larger production my DB's I get the Division by Zero error. I'll poke around and provide an updated version once I have some spare time. <br /><br />But thanks for the premise of this Query Josh, it's great to have in the tool box.<br />Anonymoushttps://www.blogger.com/profile/04535538708723066992noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-40548792956957388062014-05-02T10:08:40.339-07:002014-05-02T10:08:40.339-07:00Before 9.3, there really isn't a good way othe...Before 9.3, there really isn't a good way other than keeping track of your own administration info. Note that stats no longer reset on their own, so you'd have to send a command, or create a cron job, for them to do so.<br /><br />In 9.3 and later, many of the stats tables carry a last reset column which tells you when collection started.Josh Berkushttps://www.blogger.com/profile/09671139717468724246noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-43120876131696217342014-05-02T04:33:42.493-07:002014-05-02T04:33:42.493-07:00How does one determine "how long you've b...How does one determine "how long you've been collecting data into pg_stat_user_indexes and friends"?HT1815https://www.blogger.com/profile/12549556790078954779noreply@blogger.comtag:blogger.com,1999:blog-7476449567742726187.post-65669201727485677592014-05-01T19:12:41.606-07:002014-05-01T19:12:41.606-07:00> CTEs, otherwise known as "WITH statement...> CTEs, otherwise known as "WITH statements", are a life-saver for working with complex queries<br /><br />Yes. Ironically though they are the number one reason why I have so many unused indexes.Anonymousnoreply@blogger.com