Re: [PERFORM] Index creation running now for 14 hours

2015-08-26 Thread Igor Neyman
From: Tory M Blue [mailto:tmb...@gmail.com] Sent: Wednesday, August 26, 2015 3:26 PM To: Igor Neyman iney...@perceptron.com Cc: pgsql-performance pgsql-performance@postgresql.org Subject: Re: [PERFORM] Index creation running now for 14 hours On Wed, Aug 26, 2015 at 12:18 PM, Igor Neyman

[PERFORM] Index creation running now for 14 hours

2015-08-26 Thread Tory M Blue
I'm running 9.3.4 with slon 2.2.3, I did a drop add last night at 9pm, it started this particular tables index creation at 10:16pm and it's still running. 1 single core is at 100% (32 core box) and there is almost zero I/O activity. CentOS 6.6 16398 | clsdb | 25765 | 10 | postgres |

Re: [PERFORM] Index creation running now for 14 hours

2015-08-26 Thread Tory M Blue
On Wed, Aug 26, 2015 at 12:18 PM, Igor Neyman iney...@perceptron.com wrote: *From:* pgsql-performance-ow...@postgresql.org [mailto: pgsql-performance-ow...@postgresql.org] *On Behalf Of *Tory M Blue *Sent:* Wednesday, August 26, 2015 3:14 PM *To:* pgsql-performance

Re: [PERFORM] Index creation running now for 14 hours

2015-08-26 Thread Igor Neyman
From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Tory M Blue Sent: Wednesday, August 26, 2015 3:14 PM To: pgsql-performance pgsql-performance@postgresql.org Subject: [PERFORM] Index creation running now for 14 hours I'm running 9.3.4

Re: [PERFORM] Gist indexing performance with cidr types

2015-08-26 Thread Jeff Janes
On Wed, Aug 26, 2015 at 4:29 AM, Henrik Thostrup Jensen h...@nordu.net wrote: On Wed, 26 Aug 2015, Emre Hasegeli wrote: Are the coverage operatons just that expensive? They shouldn't be. A similar query like yours works in 0.5 second on my laptop: [snip] I get the same from your

Re: [PERFORM] Index creation running now for 14 hours

2015-08-26 Thread Tory M Blue
On Wed, Aug 26, 2015 at 2:45 PM, Qingqing Zhou zhouqq.postg...@gmail.com wrote: On Wed, Aug 26, 2015 at 1:26 PM, Tory M Blue tmb...@gmail.com wrote: Right now the 100% cpu process which is this index is only using 3.5GB and has been for the last 15 hours If 100% cpu, you can do 'sudo

Re: [PERFORM] Index creation running now for 14 hours

2015-08-26 Thread Tomas Vondra
Hi, On 08/26/2015 11:53 PM, Tory M Blue wrote: On Wed, Aug 26, 2015 at 2:45 PM, Qingqing Zhou zhouqq.postg...@gmail.com mailto:zhouqq.postg...@gmail.com wrote: On Wed, Aug 26, 2015 at 1:26 PM, Tory M Blue tmb...@gmail.com mailto:tmb...@gmail.com wrote: Right now the 100%

Re: [PERFORM] Index creation running now for 14 hours

2015-08-26 Thread Tomas Vondra
On 08/26/2015 10:26 PM, Tory M Blue wrote: the table is 90GB without indexes, 285GB with indexes and bloat, The row count is not actually completing.. 125Million rows over 13 months, this table is probably close to 600million rows. You don't need to do SELECT COUNT(*) if you only need an

Re: [PERFORM] Index creation running now for 14 hours

2015-08-26 Thread Peter Geoghegan
On Wed, Aug 26, 2015 at 3:36 PM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: But I guess the answer is, no real way to tell what the box is doing when it's creating an index. Yes there was a lock, no I could not find a way to see how it's progressing so there was no way for me to gauge

Re: [PERFORM] Index creation running now for 14 hours

2015-08-26 Thread Tory M Blue
On Wed, Aug 26, 2015 at 12:36 PM, Igor Neyman iney...@perceptron.com wrote: *From:* Tory M Blue [mailto:tmb...@gmail.com] *Sent:* Wednesday, August 26, 2015 3:26 PM *To:* Igor Neyman iney...@perceptron.com *Cc:* pgsql-performance pgsql-performance@postgresql.org *Subject:* Re: [PERFORM]

Re: [PERFORM] Index creation running now for 14 hours

2015-08-26 Thread Qingqing Zhou
On Wed, Aug 26, 2015 at 1:26 PM, Tory M Blue tmb...@gmail.com wrote: Right now the 100% cpu process which is this index is only using 3.5GB and has been for the last 15 hours If 100% cpu, you can do 'sudo perf top' to see what the CPU is busy about. Regards, Qingqing -- Sent via

Re: [PERFORM] Gist indexing performance with cidr types

2015-08-26 Thread Henrik Thostrup Jensen
On Wed, 26 Aug 2015, Henrik Thostrup Jensen wrote: Can you try 9.5 to see if they help? I'll try installing it and report back. I upgraded to 9.5 (easier than expected) and ran vacuum analyze. The query planner now chooses index scan for outer and inner join. This seems to cut off roughly

Re: [PERFORM] Gist indexing performance with cidr types

2015-08-26 Thread Henrik Thostrup Jensen
Hi, thanks for the reply. On Tue, 25 Aug 2015, Emre Hasegeli wrote: I'm trying to get a query to run fast enough for interactive use. I've gotten some speed-up, but still not there. It is for a tool called IRRExplorer (http://irrexplorer.nlnog.net/) that correlates IP routes between Internet

Re: [PERFORM] Gist indexing performance with cidr types

2015-08-26 Thread Henrik Thostrup Jensen
On Wed, 26 Aug 2015, Emre Hasegeli wrote: Are the coverage operatons just that expensive? They shouldn't be. A similar query like yours works in 0.5 second on my laptop: [snip] I get the same from your testcase. Maybe, something we haven't expected about your dataset causes a performance

Re: [PERFORM] Gist indexing performance with cidr types

2015-08-26 Thread Emre Hasegeli
Are the coverage operatons just that expensive? They shouldn't be. A similar query like yours works in 0.5 second on my laptop: # create table inner_side as select i, ((random() * 255.5)::int::text || '.' || (random() * 255.5)::int::text || '.' || (random() * 255.5)::int::text || '.' ||