Re: [Bacula-users] file table - additional indexes
On 12/23/2014 8:53 AM, Dan Langille wrote: > select * from file where fileindex = 23495 > > It took a long time. > > I added this index: create index file_fileindex on file(fileindex) ... > I am willing to sacrifice that space. I’m also willing to bed that > table updates won’t be affected by much at all. We’ll find out on 4 > Jan when the full backups are run. The issue is how often you query for a specific fileindex vs how often you update the file table. If you save 5 minutes once a year and lose 5 seconds every night, that's not such a great trade-off. Dima -- Dive into the World of Parallel Programming! The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] file table - additional indexes
From time to time the topic of indexes arises. Here is a practical example, using PostgreSQL 9.3 I was running this query: select * from file where fileindex = 23495 It took a long time. I added this index: create index file_fileindex on file(fileindex) The query takes a few seconds. Background: 268.1 million rows. Database size: Before index: 100GB After index: 106GB I am willing to sacrifice that space. I’m also willing to bed that table updates won’t be affected by much at all. We’ll find out on 4 Jan when the full backups are run. Skeptics are welcome to contact me for confirmation/denial after that. :) — Dan Langille http://langille.org/ -- Dive into the World of Parallel Programming! The Go Parallel Website, sponsored by Intel and developed in partnership with Slashdot Media, is your hub for all things parallel software development, from weekly thought leadership blogs to news, videos, case studies, tutorials and more. Take a look and join the conversation now. http://goparallel.sourceforge.net ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users