pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedStrange Create Index behaviour
12+ messages / 3 participants
[nested] [flat]
* Strange Create Index behaviour
@ 2006-02-15 20:00 Gary Doades <gpd@gpdnet.co.uk>
0 siblings, 3 replies; 12+ messages in thread
From: Gary Doades @ 2006-02-15 20:00 UTC (permalink / raw)
To: pgsql-performance
Platform: FreeBSD 6.0, Postgresql 8.1.2 compiled from the ports collection.
Not sure if this belongs in performance or bugs..
A pg_restore of my 2.5GB database was taking up to 2 hours to complete
instead of the expected 10-15 minutes. Checking the server it was mostly
CPU bound. Testing further I discovered that is was spending huge
amounts of CPU time creating some indexes.
It took a while to find out, but basically it boils down to this:
If the column that is having the index created has a certain
distribution of values then create index takes a very long time. If the
data values (integer in this case) a fairly evenly distributed then
create index is very quick, if the data values are all the same it is
very quick. I discovered that in the slow cases the column had
approximately half the values as zero and the rest fairly spread out.
One column started off with around 400,000 zeros and the rest of the
following rows spread between values of 1 to 500,000.
I have put together a test case that demonstrates the problem (see
below). I create a simple table, as close in structure to one of my
problem tables and populate an integer column with 100,000 zeros follow
by 100,000 random integers between 0 and 100,000. Then create an index
on this column. I then drop the table and repeat. The create index
should take around 1-2 seconds. A fair proportion of the time it takes
50 seconds!!!
If I fill the same row with all random data the create index always
takes a second or two. If I fill the column with all zeros everything is
still OK.
When my tables that I am trying to restore are over 2 million rows the
creating one index can take an hour!! (almost all CPU time).
All other areas of performance, once the dump is restored and analysed
seem to be OK, even large hash/merge joins and sorts
This is entirely repeatable in FreeBSD in that around half the time
create index will be incredibly slow.
All postgresql.conf settings are at the defaults for the test initially
(fresh install)
The final interesting thing is that as I increase shared buffers to 2000
or 3000 the problem gets *worse*
The following text is output from the test script..
select version();
version
------------------------------------------------------------------------------------------------
PostgreSQL 8.1.2 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC)
3.4.4 [FreeBSD] 20050518
(1 row)
\timing
Timing is on.
----- Many slow cases, note the 50+ seconds cases
create table atest(i int4, r int4,d1 timestamp, d2 timestamp);
CREATE TABLE
Time: 81.859 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),0,now(),now();
INSERT 0 100000
Time: 1482.141 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 1543.508 ms
create index idx on atest(r);
CREATE INDEX
Time: 56685.230 ms
drop table atest;
DROP TABLE
Time: 4.616 ms
create table atest(i int4, r int4,d1 timestamp, d2 timestamp);
CREATE TABLE
Time: 6.889 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),0,now(),now();
INSERT 0 100000
Time: 2009.787 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 1828.663 ms
create index idx on atest(r);
CREATE INDEX
Time: 3991.257 ms
drop table atest;
DROP TABLE
Time: 3.796 ms
create table atest(i int4, r int4,d1 timestamp, d2 timestamp);
CREATE TABLE
Time: 19.965 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),0,now(),now();
INSERT 0 100000
Time: 1625.059 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 2622.827 ms
create index idx on atest(r);
CREATE INDEX
Time: 1082.799 ms
drop table atest;
DROP TABLE
Time: 4.627 ms
create table atest(i int4, r int4,d1 timestamp, d2 timestamp);
CREATE TABLE
Time: 2.953 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),0,now(),now();
INSERT 0 100000
Time: 2068.744 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 2671.420 ms
create index idx on atest(r);
CREATE INDEX
Time: 8047.660 ms
drop table atest;
DROP TABLE
Time: 3.675 ms
create table atest(i int4, r int4,d1 timestamp, d2 timestamp);
CREATE TABLE
Time: 2.582 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),0,now(),now();
INSERT 0 100000
Time: 1723.987 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 2263.131 ms
create index idx on atest(r);
CREATE INDEX
Time: 50050.308 ms
drop table atest;
DROP TABLE
Time: 52.744 ms
create table atest(i int4, r int4,d1 timestamp, d2 timestamp);
CREATE TABLE
Time: 25.370 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),0,now(),now();
INSERT 0 100000
Time: 2052.733 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 2631.317 ms
create index idx on atest(r);
CREATE INDEX
Time: 61440.897 ms
drop table atest;
DROP TABLE
Time: 26.137 ms
create table atest(i int4, r int4,d1 timestamp, d2 timestamp);
CREATE TABLE
Time: 24.794 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),0,now(),now();
INSERT 0 100000
Time: 2851.977 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 1553.046 ms
create index idx on atest(r);
CREATE INDEX
Time: 1774.920 ms
---- Fast (Normal?) cases
drop table atest;
DROP TABLE
Time: 4.422 ms
create table atest(i int4, r int4,d1 timestamp, d2 timestamp);
CREATE TABLE
Time: 2.543 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 1516.246 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 1407.400 ms
create index idx on atest(r);
CREATE INDEX
Time: 903.503 ms
drop table atest;
DROP TABLE
Time: 3.820 ms
create table atest(i int4, r int4,d1 timestamp, d2 timestamp);
CREATE TABLE
Time: 22.861 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 1455.556 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 2037.996 ms
create index idx on atest(r);
CREATE INDEX
Time: 718.286 ms
drop table atest;
DROP TABLE
Time: 4.503 ms
create table atest(i int4, r int4,d1 timestamp, d2 timestamp);
CREATE TABLE
Time: 3.448 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 1523.540 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 1261.473 ms
create index idx on atest(r);
CREATE INDEX
Time: 727.707 ms
drop table atest;
DROP TABLE
Time: 3.564 ms
create table atest(i int4, r int4,d1 timestamp, d2 timestamp);
CREATE TABLE
Time: 2.897 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 1447.504 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 1403.525 ms
create index idx on atest(r);
CREATE INDEX
Time: 754.577 ms
drop table atest;
DROP TABLE
Time: 4.633 ms
create table atest(i int4, r int4,d1 timestamp, d2 timestamp);
CREATE TABLE
Time: 3.196 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 1618.544 ms
insert into atest (i,r,d1,d2) select
generate_series(1,100000),random()*100000,now(),now();
INSERT 0 100000
Time: 1530.450 ms
create index idx on atest(r);
CREATE INDEX
Time: 802.980 ms
drop table atest;
DROP TABLE
Time: 4.707 ms
mserver#
Regards,
Gary.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Strange Create Index behaviour
@ 2006-02-15 20:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gary Doades <gpd@gpdnet.co.uk>
2 siblings, 3 replies; 12+ messages in thread
From: Tom Lane @ 2006-02-15 20:56 UTC (permalink / raw)
To: Gary Doades <gpd@gpdnet.co.uk>; +Cc: pgsql-performance
Gary Doades <gpd@gpdnet.co.uk> writes:
> Platform: FreeBSD 6.0, Postgresql 8.1.2 compiled from the ports collection.
> If the column that is having the index created has a certain
> distribution of values then create index takes a very long time. If the
> data values (integer in this case) a fairly evenly distributed then
> create index is very quick, if the data values are all the same it is
> very quick. I discovered that in the slow cases the column had
> approximately half the values as zero and the rest fairly spread out.
Interesting. I tried your test script and got fairly close times
for all the cases on two different machines:
old HPUX machine: shortest 5800 msec, longest 7960 msec
new Fedora 4 machine: shortest 461 msec, longest 608 msec
(the HPUX machine was doing other stuff at the same time, so some
of its variation is probably only noise).
So what this looks like to me is a corner case that FreeBSD's qsort
fails to handle well.
You might try forcing Postgres to use our private copy of qsort, as we
do on Solaris for similar reasons. (The easy way to do this by hand
is to configure as normal, then alter the LIBOBJS setting in
src/Makefile.global to add "qsort.o", then proceed with normal build.)
However, I think that our private copy is descended from *BSD sources,
so it might have the same failure mode. It'd be worth finding out.
> The final interesting thing is that as I increase shared buffers to 2000
> or 3000 the problem gets *worse*
shared_buffers is unlikely to impact index build time noticeably in
recent PG releases. maintenance_work_mem would affect it a lot, though.
What setting were you using for that?
Can anyone else try these test cases on other platforms?
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Strange Create Index behaviour
@ 2006-02-15 21:06 Gary Doades <gpd@gpdnet.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 12+ messages in thread
From: Gary Doades @ 2006-02-15 21:06 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
> Interesting. I tried your test script and got fairly close times
> for all the cases on two different machines:
> old HPUX machine: shortest 5800 msec, longest 7960 msec
> new Fedora 4 machine: shortest 461 msec, longest 608 msec
> (the HPUX machine was doing other stuff at the same time, so some
> of its variation is probably only noise).
>
> So what this looks like to me is a corner case that FreeBSD's qsort
> fails to handle well.
>
> You might try forcing Postgres to use our private copy of qsort, as we
> do on Solaris for similar reasons. (The easy way to do this by hand
> is to configure as normal, then alter the LIBOBJS setting in
> src/Makefile.global to add "qsort.o", then proceed with normal build.)
> However, I think that our private copy is descended from *BSD sources,
> so it might have the same failure mode. It'd be worth finding out.
>
>> The final interesting thing is that as I increase shared buffers to 2000
>> or 3000 the problem gets *worse*
>
> shared_buffers is unlikely to impact index build time noticeably in
> recent PG releases. maintenance_work_mem would affect it a lot, though.
> What setting were you using for that?
>
> Can anyone else try these test cases on other platforms?
>
Thanks for that.
I've since tried it on Windows (pg 8.1.2) and the times were all
similar, around 1200ms so it might just be BSD.
I'll have to wait until tomorrow to get back to my BSD box. FreeBSD
ports makes it easy to install, so I'll have to figure out how to get in
and change things manually. I guess the appropriate files are still left
around after the ports make command finishes, so I just edit the file
and make again?
If it can't be fixed though I guess we may have a problem using BSD. I'm
surprised this hasn't been brought up before, the case doesn't seem
*that* rare. Maybe not that many using FreeBSD?
I'd certainly be interested if anyone else can repro it on FreeBSD though.
Regards,
Gary.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Strange Create Index behaviour
@ 2006-02-15 21:11 Gary Doades <gpd@gpdnet.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 12+ messages in thread
From: Gary Doades @ 2006-02-15 21:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
> shared_buffers is unlikely to impact index build time noticeably in
> recent PG releases. maintenance_work_mem would affect it a lot, though.
> What setting were you using for that?
>
Also, i tried upping maintenance_work_mem to 65536 and it didn't make
much difference (maybe 10% faster for the "normal" cases). Upping the
shared_buffers *definitely* makes the bad cases worse though, but I
agree I don't see why...
Regards,
Gary.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Strange Create Index behaviour
@ 2006-02-15 21:27 Simon Riggs <simon@2ndquadrant.com>
parent: Gary Doades <gpd@gpdnet.co.uk>
2 siblings, 1 reply; 12+ messages in thread
From: Simon Riggs @ 2006-02-15 21:27 UTC (permalink / raw)
To: Gary Doades <gpd@gpdnet.co.uk>; +Cc: pgsql-performance
On Wed, 2006-02-15 at 20:00 +0000, Gary Doades wrote:
> I have put together a test case
Please enable trace_sort=on and then repeat tests and post the
accompanying log file.
I think this is simply the sort taking longer depending upon the data
distribution, but I'd like to know for sure.
Thanks,
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Strange Create Index behaviour
@ 2006-02-15 21:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 2 replies; 12+ messages in thread
From: Tom Lane @ 2006-02-15 21:27 UTC (permalink / raw)
To: Gary Doades <gpd@gpdnet.co.uk>; +Cc: pgsql-performance
I wrote:
> Interesting. I tried your test script and got fairly close times
> for all the cases on two different machines:
> old HPUX machine: shortest 5800 msec, longest 7960 msec
> new Fedora 4 machine: shortest 461 msec, longest 608 msec
> So what this looks like to me is a corner case that FreeBSD's qsort
> fails to handle well.
I tried forcing PG to use src/port/qsort.c on the Fedora machine,
and lo and behold:
new Fedora 4 machine: shortest 434 msec, longest 8530 msec
So it sure looks like this script does expose a problem on BSD-derived
qsorts. Curiously, the case that's much the worst for me is the third
in the script, while the shortest time is the first case, which was slow
for Gary. So I'd venture that the *BSD code has been tweaked somewhere
along the way, in a manner that moves the problem around without really
fixing it. (Anyone want to compare the actual FreeBSD source to what
we have?)
This is pretty relevant stuff, because there was a thread recently
advocating that we stop using the platform qsort on all platforms:
http://archives.postgresql.org/pgsql-hackers/2005-12/msg00610.php
It's really interesting to see a case where port/qsort is radically
worse than other qsorts ... unless we figure that out and fix it,
I think the idea of using port/qsort everywhere has just taken a
major hit.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Strange Create Index behaviour
@ 2006-02-15 21:34 Gary Doades <gpd@gpdnet.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 12+ messages in thread
From: Gary Doades @ 2006-02-15 21:34 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
> I tried forcing PG to use src/port/qsort.c on the Fedora machine,
> and lo and behold:
> new Fedora 4 machine: shortest 434 msec, longest 8530 msec
>
> So it sure looks like this script does expose a problem on BSD-derived
> qsorts. Curiously, the case that's much the worst for me is the third
> in the script, while the shortest time is the first case, which was slow
> for Gary. So I'd venture that the *BSD code has been tweaked somewhere
> along the way, in a manner that moves the problem around without really
> fixing it. (Anyone want to compare the actual FreeBSD source to what
> we have?)
>
If I run the script again, it is not always the first case that is slow,
it varies from run to run, which is why I repeated it quite a few times
for the test.
Interestingly, if I don't delete the table after a run, but just drop
and re-create the index repeatedly it stays a pretty consistent time,
either repeatedly good or repeatedly bad!
Regards,
Gary.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Strange Create Index behaviour
@ 2006-02-15 21:47 Gary Doades <gpd@gpdnet.co.uk>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 12+ messages in thread
From: Gary Doades @ 2006-02-15 21:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Tom Lane wrote:
>
> So it sure looks like this script does expose a problem on BSD-derived
> qsorts. Curiously, the case that's much the worst for me is the third
> in the script, while the shortest time is the first case, which was slow
> for Gary. So I'd venture that the *BSD code has been tweaked somewhere
> along the way, in a manner that moves the problem around without really
> fixing it. (Anyone want to compare the actual FreeBSD source to what
> we have?)
>
> It's really interesting to see a case where port/qsort is radically
> worse than other qsorts ... unless we figure that out and fix it,
> I think the idea of using port/qsort everywhere has just taken a
> major hit.
>
More specifically to BSD, is there any way I can use a non-BSD qsort for
building Postresql server?
Regards,
Gary.
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Strange Create Index behaviour
@ 2006-02-15 21:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Simon Riggs <simon@2ndquadrant.com>
0 siblings, 0 replies; 12+ messages in thread
From: Tom Lane @ 2006-02-15 21:48 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Gary Doades <gpd@gpdnet.co.uk>; pgsql-performance
Simon Riggs <simon@2ndquadrant.com> writes:
> Please enable trace_sort=on and then repeat tests and post the
> accompanying log file.
I did this on my Fedora machine with port/qsort.c, and got the results
attached. Curiously, this run has the spikes in completely different
places than the prior one did. So the random component of the test data
is affecting the results quite a lot. There seems absolutely no doubt
that we are looking at data-dependent qsort misbehavior, though. The
CPU time eaten by performsort accounts for all but about 100 msec of the
elapsed time reported on the psql side.
regards, tom lane
LOG: begin index sort: unique = f, workMem = 16384, randomAccess = f
LOG: performsort starting: CPU 0.00s/0.15u sec elapsed 0.15 sec
LOG: performsort done: CPU 0.00s/12.43u sec elapsed 12.44 sec
LOG: internal sort ended, 9861 KB used: CPU 0.01s/12.51u sec elapsed 12.52 sec
LOG: begin index sort: unique = f, workMem = 16384, randomAccess = f
LOG: performsort starting: CPU 0.00s/0.14u sec elapsed 0.15 sec
LOG: performsort done: CPU 0.00s/0.78u sec elapsed 0.78 sec
LOG: internal sort ended, 9861 KB used: CPU 0.02s/0.85u sec elapsed 0.87 sec
LOG: begin index sort: unique = f, workMem = 16384, randomAccess = f
LOG: performsort starting: CPU 0.01s/0.14u sec elapsed 0.15 sec
LOG: performsort done: CPU 0.01s/0.96u sec elapsed 0.97 sec
LOG: internal sort ended, 9861 KB used: CPU 0.02s/1.03u sec elapsed 1.06 sec
LOG: begin index sort: unique = f, workMem = 16384, randomAccess = f
LOG: performsort starting: CPU 0.00s/0.14u sec elapsed 0.15 sec
LOG: performsort done: CPU 0.00s/0.31u sec elapsed 0.32 sec
LOG: internal sort ended, 9861 KB used: CPU 0.02s/0.38u sec elapsed 0.40 sec
LOG: begin index sort: unique = f, workMem = 16384, randomAccess = f
LOG: performsort starting: CPU 0.00s/0.14u sec elapsed 0.15 sec
LOG: performsort done: CPU 0.00s/7.91u sec elapsed 7.92 sec
LOG: internal sort ended, 9861 KB used: CPU 0.02s/7.99u sec elapsed 8.01 sec
LOG: begin index sort: unique = f, workMem = 16384, randomAccess = f
LOG: performsort starting: CPU 0.01s/0.13u sec elapsed 0.15 sec
LOG: performsort done: CPU 0.01s/0.61u sec elapsed 0.63 sec
LOG: internal sort ended, 9861 KB used: CPU 0.04s/0.67u sec elapsed 0.71 sec
LOG: begin index sort: unique = f, workMem = 16384, randomAccess = f
LOG: performsort starting: CPU 0.01s/0.13u sec elapsed 0.15 sec
LOG: performsort done: CPU 0.01s/11.52u sec elapsed 11.54 sec
LOG: internal sort ended, 9861 KB used: CPU 0.03s/11.59u sec elapsed 11.62 sec
LOG: begin index sort: unique = f, workMem = 16384, randomAccess = f
LOG: performsort starting: CPU 0.00s/0.14u sec elapsed 0.15 sec
LOG: performsort done: CPU 0.00s/0.45u sec elapsed 0.46 sec
LOG: internal sort ended, 9861 KB used: CPU 0.02s/0.55u sec elapsed 0.57 sec
LOG: begin index sort: unique = f, workMem = 16384, randomAccess = f
LOG: performsort starting: CPU 0.00s/0.14u sec elapsed 0.15 sec
LOG: performsort done: CPU 0.00s/0.45u sec elapsed 0.46 sec
LOG: internal sort ended, 9861 KB used: CPU 0.04s/0.54u sec elapsed 0.57 sec
LOG: begin index sort: unique = f, workMem = 16384, randomAccess = f
LOG: performsort starting: CPU 0.02s/0.12u sec elapsed 0.15 sec
LOG: performsort done: CPU 0.02s/0.44u sec elapsed 0.46 sec
LOG: internal sort ended, 9861 KB used: CPU 0.03s/0.55u sec elapsed 0.58 sec
LOG: begin index sort: unique = f, workMem = 16384, randomAccess = f
LOG: performsort starting: CPU 0.02s/0.13u sec elapsed 0.15 sec
LOG: performsort done: CPU 0.02s/0.44u sec elapsed 0.46 sec
LOG: internal sort ended, 9861 KB used: CPU 0.03s/0.54u sec elapsed 0.58 sec
LOG: begin index sort: unique = f, workMem = 16384, randomAccess = f
LOG: performsort starting: CPU 0.02s/0.13u sec elapsed 0.15 sec
LOG: performsort done: CPU 0.02s/0.44u sec elapsed 0.46 sec
LOG: internal sort ended, 9861 KB used: CPU 0.04s/0.54u sec elapsed 0.59 sec
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Strange Create Index behaviour
@ 2006-02-15 21:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gary Doades <gpd@gpdnet.co.uk>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2006-02-15 21:51 UTC (permalink / raw)
To: Gary Doades <gpd@gpdnet.co.uk>; +Cc: pgsql-performance
Gary Doades <gpd@gpdnet.co.uk> writes:
> Interestingly, if I don't delete the table after a run, but just drop
> and re-create the index repeatedly it stays a pretty consistent time,
> either repeatedly good or repeatedly bad!
This is consistent with the theory of a data-dependent performance
problem in qsort. If you don't generate a fresh set of random test
data, then you get repeatable runtimes. With a new set of test data,
you might or might not hit the not-so-sweet-spot that we seem to have
detected.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Strange Create Index behaviour
@ 2006-02-15 23:51 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 12+ messages in thread
From: Simon Riggs @ 2006-02-15 23:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gary Doades <gpd@gpdnet.co.uk>; pgsql-performance
On Wed, 2006-02-15 at 16:51 -0500, Tom Lane wrote:
> Gary Doades <gpd@gpdnet.co.uk> writes:
> > Interestingly, if I don't delete the table after a run, but just drop
> > and re-create the index repeatedly it stays a pretty consistent time,
> > either repeatedly good or repeatedly bad!
>
> This is consistent with the theory of a data-dependent performance
> problem in qsort. If you don't generate a fresh set of random test
> data, then you get repeatable runtimes. With a new set of test data,
> you might or might not hit the not-so-sweet-spot that we seem to have
> detected.
Agreed. Good analysis...
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Strange Create Index behaviour
@ 2006-02-16 01:52 Simon Riggs <simon@2ndquadrant.com>
parent: Gary Doades <gpd@gpdnet.co.uk>
2 siblings, 0 replies; 12+ messages in thread
From: Simon Riggs @ 2006-02-16 01:52 UTC (permalink / raw)
To: Gary Doades <gpd@gpdnet.co.uk>; +Cc: pgsql-performance
On Wed, 2006-02-15 at 20:00 +0000, Gary Doades wrote:
> I have put together a test case that demonstrates the problem (see
> below). I create a simple table, as close in structure to one of my
> problem tables and populate an integer column with 100,000 zeros follow
> by 100,000 random integers between 0 and 100,000. Then create an index
> on this column. I then drop the table and repeat. The create index
> should take around 1-2 seconds. A fair proportion of the time it takes
> 50 seconds!!!
>
> If I fill the same row with all random data the create index always
> takes a second or two. If I fill the column with all zeros everything is
> still OK.
Aside from the importance of investigating sort behaviour, have you
tried to build a partial index WHERE col > 0 ? That way you wouldn't
even be indexing the zeros.
Best Regards, Simon Riggs
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2006-02-16 01:52 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-02-15 20:00 Strange Create Index behaviour Gary Doades <gpd@gpdnet.co.uk>
2006-02-15 20:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-15 21:06 ` Gary Doades <gpd@gpdnet.co.uk>
2006-02-15 21:11 ` Gary Doades <gpd@gpdnet.co.uk>
2006-02-15 21:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-15 21:34 ` Gary Doades <gpd@gpdnet.co.uk>
2006-02-15 21:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-15 23:51 ` Simon Riggs <simon@2ndquadrant.com>
2006-02-15 21:47 ` Gary Doades <gpd@gpdnet.co.uk>
2006-02-15 21:27 ` Simon Riggs <simon@2ndquadrant.com>
2006-02-15 21:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-02-16 01:52 ` Simon Riggs <simon@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox