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.
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
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
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
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
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
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:
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
>
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 dro
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.
On Thu, May 22, 2008 at 9:18 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> Ermm .. this is in fact mostly broken in 8.3.0 and 8.3.1. If you don't
> want to wait for 8.3.2, you need this patch:
> http://archives.postgresql.org/pgsql-committers/2008-03/msg00566.php
That's what I had in mind. We have to
"Guillaume Smet" <[EMAIL PROTECTED]> writes:
> On Thu, May 22, 2008 at 3:14 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
>> Do you have maintenance_work_mem set large enough that the index
>> creation sort is done in-memory? 8.1 depends on the platform's qsort
>> and a lot of them are kinda pessimal fo
On Thu, May 22, 2008 at 6:50 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> Just curious, what happens if you create the date index first, then
> the clazz one?
It's not due to any cache effect if it's your question. It's mostly
CPU time and changing the order doesn't change the behaviour.
I'll m
On Thu, May 22, 2008 at 6:32 AM, Guillaume Smet
<[EMAIL PROTECTED]> wrote:
> Hi -performance,
>
>
> LOG: duration: 1636301.317 ms statement: CREATE INDEX
> index_journal_clazz ON journal USING btree (clazz);
> LOG: duration: 20613.009 ms statement: CREATE INDEX
> index_journal_date ON journal U
On Thu, 22 May 2008, Tom Lane wrote:
Do you have maintenance_work_mem set large enough that the index
creation sort is done in-memory? 8.1 depends on the platform's qsort
and a lot of them are kinda pessimal for input like this.
Looking at the fact that other indexes on the same table are crea
On Thu, May 22, 2008 at 3:14 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> Do you have maintenance_work_mem set large enough that the index
> creation sort is done in-memory? 8.1 depends on the platform's qsort
> and a lot of them are kinda pessimal for input like this.
FWIW, it's a 32 bits CentOS 4.
"Guillaume Smet" <[EMAIL PROTECTED]> writes:
> I experienced this morning a performance problem when we imported a
> dump in a 8.1 database.
> The table is 5 millions rows large and when the dump creates an index
> on a specific text column called clazz it takes 27 minutes while on
> the other colu
Hi -performance,
I experienced this morning a performance problem when we imported a
dump in a 8.1 database.
The table is 5 millions rows large and when the dump creates an index
on a specific text column called clazz it takes 27 minutes while on
the other columns, it only takes a couple of secon
On Wed, 7 Jan 2004, Eric Jain wrote:
> Any tips for speeding up index creation?
>
> I need to bulk load a large table with 100M rows and several indexes,
> some of which span two columns.
>
> By dropping all indexes prior to issuing the 'copy from' command, the
> operation completes 10x as fast
On Wed, 7 Jan 2004 18:08:06 +0100
"Eric Jain" <[EMAIL PROTECTED]> wrote:
> Any tips for speeding up index creation?
>
> I need to bulk load a large table with 100M rows and several indexes,
> some of which span two columns.
>
> By dropping all indexes prior to issuing the 'copy from' command, th
Any tips for speeding up index creation?
I need to bulk load a large table with 100M rows and several indexes,
some of which span two columns.
By dropping all indexes prior to issuing the 'copy from' command, the
operation completes 10x as fast (1.5h vs 15h).
Unfortunately, recreating a single i
Chester Kustarz <[EMAIL PROTECTED]> writes:
> it seems that you cannot run analyze inside a transaction:
You can in 7.3.* ...
regards, tom lane
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unreg
> begin;
> analyze foo;
> ERROR: ANALYZE cannot run inside a BEGIN/END block
>
> i am using version 7.2.3.
Time to upgrade. 7.3 / 7.4 allows this to happen.
signature.asc
Description: This is a digitally signed message part
is there any way to update the stats inside a transaction? what i have is
something like:
select count(*) from foo;
-> 0
begin;
copy foo from '/tmp/foo'; -- about 100k rows
-- run some queries on foo which perform horribly because the stats
-- are way off (100k rows v. 0 rows)
commit;
it see
At 13:40 10/31/2003, Neil Conway wrote:
On Fri, 2003-10-31 at 13:27, Allen Landsidel wrote:
> I had no idea analyze was playing such a big role in this sense.. I really
> thought that other than saving space, it wasn't doing much for tables that
> don't have indexes on the.
ANALYZE doesn't save any
Allen,
> I had no idea analyze was playing such a big role in this sense.. I really
> thought that other than saving space, it wasn't doing much for tables that
> don't have indexes on the.
Among other things, ANALYZE tells postgres how many rows are in the table. So
if you add a PK constraint
On Fri, 2003-10-31 at 13:27, Allen Landsidel wrote:
> I had no idea analyze was playing such a big role in this sense.. I really
> thought that other than saving space, it wasn't doing much for tables that
> don't have indexes on the.
ANALYZE doesn't save any space at all -- VACUUM is probably w
Nope, still 7.3.4 here.. I am very excited about 7.4 though.. almost as
excited as I am about FreeBSD 5.x going -STABLE.. it's a close race
between the two..
I'll keep this in mind for when I update though, thanks.
At 11:23 10/31/2003, Rod Taylor wrote:
If it is 7.4 beta 5 or later, I would de
At 12:10 10/31/2003, Josh Berkus wrote:
Allen,
> a) CREATE TABLE with no indexes or keys. Run the COPY (fast, ~30min), then
> CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk and
> each fk needed.
Did you ANALYZE after the copy?
No, and this was my major mistake. I normally
Allen,
> a) CREATE TABLE with no indexes or keys. Run the COPY (fast, ~30min), then
> CREATE INDEX on each column it's needed on, and ALTER TABLE for the pk and
> each fk needed.
Did you ANALYZE after the copy?
> If there isn't a significant difference between all of them, performance
> wise, I
If it is 7.4 beta 5 or later, I would definitely go with A.
Adding indexes after the fact seems to be much quicker. Foreign keys use
the same algorithm prior to beta 5 regardless of timing.
A primary key and unique index will have approx the same performance (a
check for NULL isn't very costly).
Yet another question.. thanks to everyone responding to all these so far.. ;)
This one is basically.. given I have a big table already in COPY format,
about 28 million rows, all keys guaranteed to be unique, I'm trying to find
out which of the following will get the import finished the fastest:
32 matches
Mail list logo
