Thread: GIST create index very very slow
CREATE INDEX USING GIST(timerange); On 1.3 million rows this took only 30 seconds. on 70 million its already taken over a day. I swear it didn't take this long on version 9.3 Is there some kind of known bug with GIST? CPU is at 4% or less and ram is at 150mbs IO usage is at 100% but most of it is writes? (like 3.5mbps!) which looks good but actually the size of the disk is only increasing by like 8 BYTES per second. This is really odd and I don't want to wait an indefinite amount of time. -- View this message in context: http://postgresql.1045698.n5.nabble.com/GIST-create-index-very-very-slow-tp5815011.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Fri, Aug 15, 2014 at 5:59 PM, worthy7 <worthy.vii@gmail.com> wrote: > CREATE INDEX USING GIST(timerange); > > On 1.3 million rows this took only 30 seconds. > on 70 million its already taken over a day. I swear it didn't take this long > on version 9.3 > > > Is there some kind of known bug with GIST? CPU is at 4% or less and ram is > at 150mbs > IO usage is at 100% but most of it is writes? (like 3.5mbps!) which looks > good but actually the size of the disk is only increasing by like 8 BYTES > per second. > > This is really odd and I don't want to wait an indefinite amount of time. Sounds pretty weird, but I'm not sure anybody here will be able to help unless you can provide a more detailed problem report, such as a careful comparison of 9.3 vs. 9.4 behavior. Off-hand, the only thing that occurs to me is that a nearly-full disk often has much worse performance than one with some free space remaining, because the system is no longer able to find chunks of consecutive free space. But even if that's an issue, 8 bytes per second is sort of ridiculous. I think something's missing from your report, though, because if there is 3.5 Mb/s of write I/O and only 8 b/s of file growth, nearly all of the writes are doing something other than extending that file. If you can track down what that other thing is, it might shed quite a bit of light on the situation. You might also want to verify that you're using the same maintenance_work_mem setting on both versions. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 08/18/2014 07:47 PM, Robert Haas wrote: > I think something's missing from your report, though, because if there > is 3.5 Mb/s of write I/O and only 8 b/s of file growth, nearly all of > the writes are doing something other than extending that file. Hmm. Sounds like it's churning through temporary files. Gist build creates a bunch of buffers that spill to disk, to temporary files, until it begins writing the tuples out to the actual relation. But there haven't been any changes to that since 9.1, when the new gist build algorithm was introduced. Make sure you have maintenance_work_mem set high enough; that affects the size of the gist build buffers. Worthy7, you'll have to provide a lot more details for anyone to help you further. A reproducible test case would be ideal. - Heikki
On Mon, Aug 18, 2014 at 5:47 PM, Robert Haas <robertmhaas@gmail.com> wrote: > Sounds pretty weird I recall GIST being really slow in the distant past in cases where the page split choices were really bad. Is timerange an interval? Or a Range?I wonder if the pagesplit function for some of the newish data types like ranges might sometimes make bad choices. -- greg