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 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 when >> it would be done.

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 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 mailto:zhouqq.postg...@gmail.com>> wrote: On Wed, Aug 26, 2015 at 1:26 PM, Tory M Blue mailto:tmb...@gmail.com>> wrote: > > Right now the 100% cpu process which is this index is only us

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 wrote: > On Wed, Aug 26, 2015 at 1:26 PM, Tory M Blue 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

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 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 pgsql-performance mail

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 wrote: > > > > > *From:* Tory M Blue [mailto:tmb...@gmail.com] > *Sent:* Wednesday, August 26, 2015 3:26 PM > *To:* Igor Neyman > *Cc:* pgsql-performance > *Subject:* Re: [PERFORM] Index creation running now for 14 hours > > > > > > > > On Wed, Aug

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 Cc: pgsql-performance Subject: Re: [PERFORM] Index creation running now for 14 hours On Wed, Aug 26, 2015 at 12:18 PM, Igor Neyman mailto:iney...@perceptron.com>> wrote: From: pgsql-perfo

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 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 > *Subject:* [PERFORM] Index creation

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 Subject: [PERFORM] Index creation running now for 14 hours I'm running 9.3.4 with slon 2.2.3, I did a drop add la

[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 | slon.

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 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 tes

Re: [PERFORM] Gist indexing performance with cidr types

2015-08-26 Thread Emre Hasegeli
> Then there is the mixed IPv6 and IPv4 data that might factor in. It shouldn't be the problem. The index should separate them on the top level. > I tried the approach from your benchmark, to try make a more isolated test > case: Can you try to isolate it even more by something like this: sele

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 || >'.' || (

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 Ro