From: "Craig McIlwee" <craig.mcilwee@openroadsconsulting.com>
Subject: Re: Slow performance when querying millions of rows
To: pgsql-performance@postgresql.org
In-Reply-To: <4E0A581D.2010702@fuzzy.cz>
Message-ID: <20110628232643.3a3e6337@mail.openroadsconsulting.com>
Date: Tue, 28 Jun 2011 19:26:43 -0400
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----------MIME--2118040468-41-delim"

This is a multi-part message in MIME format.

------------MIME--2118040468-41-delim
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

=3E Dne 28=2E6=2E2011 23=3A28=2C Craig McIlwee napsal=28a=29=3A
=3E =3E Daily table explain analyze=3A http=3A//explain=2Edepesz=2Ecom/s/iL=
Y
=3E =3E Half month table explain analyze=3A http=3A//explain=2Edepesz=2Ecom=
/s/Unt
=3E=20
=3E Are you sure those two queries are exactly the same=3F Because the dail=
y
=3E case output says the width is 50B=2C while the half-month case says it=
=27s
=3E 75B=2E This might be why the sort/aggregate steps are switched=2C and t=
hat
=3E increases the amount of data so that it has to be sorted on disk =28whi=
ch
=3E is why the half-month is so much slower=29=2E
=3E=20
=3E Haven=27t you added some columns to the half-month query=3F

The daily tables were created using CREATE TABLE AS from the half month tab=
les=2C structure is the same with the exception of fill factor=2E  Queries=
 are identical except for the name of the master table that they select fro=
m=2E

=3E=20
=3E =3E Postgres version=3A
=3E =3E PostgreSQL 8=2E4=2E8=2C compiled by Visual C++ build 1400=2C 32-bit=

=3E =3E=20
=3E =3E History=3A
=3E =3E None=2C this is a new database and application
=3E =3E=20
=3E =3E Hardware=3A
=3E =3E - 2 Intel Xeon 2=2E13GHz processors with 8 cores each
=3E =3E - 8GB RAM
=3E =3E - Disks are in a RAID 5 configuration with a PERC H700 Integrated R=
AID
=3E =3E Controller 512MB Cache
=3E =3E - 5 disks=2C Seagate 7200 RPM SAS=2C 500GB each for a total capacit=
y of
=3E =3E about 2TB
=3E =3E - Windows Server 2008 R2 64-bit =28but 32-bit postgres=29
=3E =3E - Hardware upgrades arent an option at this point due to budget and=
 time
=3E =3E constraints
=3E=20
=3E Not much experience with PostgreSQL on Windows=2C but this looks good t=
o
=3E me=2E Not sure if RAID5 is a good choice=2C especially because of write=

=3E performance - this is probably one of the reasons why the disk sort is=

=3E so slow =28in the half-month case=29=2E

Yes=2C the data import is painfully slow but I hope to make up for that wit=
h the read performance later=2E

=3E=20
=3E And it=27s nice you have 8 cores=2C but don=27t forget each query execu=
tes on
=3E a single background process=2C i=2Ee=2E it may use single core=2E So th=
e above
=3E query can=27t use 8 cores - that=27s why the in-memory sort takes so lo=
ng I
=3E guess=2E
=3E=20
=3E =3E Maintenance Setup=3A
=3E =3E Autovacuum is disabled for these tables since the data is never
=3E =3E updated=2E  The tables that we are testing with at the moment will=
 not
=3E =3E grow any larger and have been both clustered and analyzed=2E  They=
 were
=3E =3E clustered on the vds=5Fdetector=5Fdata=5Ftimestamp=5Fidx index=2E=

=3E =3E=20
=3E =3E GUC Settings=3A
=3E =3E effective=5Fcache=5Fsize=3A 2048MB
=3E =3E work=5Fmem=3A 512MB
=3E =3E shared=5Fbuffers=3A 64MB=2C 512MB=2C and 1024MB=2C each yielded the=
 same query
=3E =3E plan and took the same amount of time to execute give or take a few=

=3E seconds
=3E =3E=20
=3E =3E Summary=3A
=3E =3E=20
=3E =3E The time to get the raw data =28before aggregation and sorting=29 i=
s
=3E =3E relatively similar between the daily and half month tables=2E  It w=
ould
=3E =3E appear that the major difference is the ordering of sort and
=3E =3E aggregation=2C the daily tables aggregate first so the amount of da=
ta
=3E =3E sorted is significantly less=2E
=3E=20
=3E Yes=2C the ordering is the problem=2E The amount of data to sort is so=
 huge
=3E =283GB=29 it does not fit into work=5Fmem and has to be sorted on disk=
=2E Not
=3E sure why this happens=2C the only difference I=27ve noticed is the =27w=
idth=27
=3E =2850B vs=2E 75B=29=2E Are those two queries exactly the same=3F
=3E=20
=3E =3E Since the daily tables are only 360MB=2C I would hope that the enti=
re
=3E =3E table could be pulled into memory with one large sequential read=2E=
  Of
=3E =3E course this assumes that the file pieces are stored contiguously=2C=
 but
=3E =3E auto defrag is enabled and shows low fragmentation so Im trusting=
 =28as
=3E =3E much as one can=29 Windows to do the right thing here=2E  My drives=
 have a
=3E =3E 150MB/s sustained max throughput=2C and considering that data is sp=
read
=3E =3E across 5 drives I would hope to at least be able to reach the singl=
e
=3E =3E disk theoretical limit and read an entire table plus the index into=

=3E =3E memory about 4 to 5 seconds=2E  Based on the analyze output=2C each=
 daily
=3E =3E table averages 6 to 7 seconds=2C so Im pretty close there and maybe=
 just
=3E =3E limited by disk speed=3F
=3E=20
=3E Well=2C you have 30 partitions and 7 seconds for each means 210 secons=
 in
=3E total=2E Which is about the time you get =28before the aggregate/sort=
=29=2E
=3E=20
=3E You have to check where the bottleneck is - is it the I/O or CPU=3F I=
=27d
=3E guess the CPU=2C but I may be wrong=2E On unix I=27d use something like=

=3E iostat/vmstat/top to see what=27s going on - not sure what to use on
=3E Windows=2E I guess there is a some console or maybe Process Explorer fr=
om
=3E sysinternals=2E
=3E=20
=3E =3E In both cases=2C the row estimates vs actual are way off=2E  Ive in=
creased
=3E =3E statistics on the reading=5Ftimestamp and road=5Fname columns to 10=
0 and
=3E =3E then 1000 with no change=2E  I ran an ANALYZE after each statistics=

=3E =3E change=2E  Should I be upping stats on the non-indexed columns as w=
ell=3F=20
=3E =3E Ive read documentation that says I should be able to set statistics=

=3E =3E values for an entire table as opposed to per column=2C but havent f=
ound
=3E =3E how to do that=2E  I guess I was either too lazy to update statisti=
cs on
=3E =3E each column or just didnt think it would help much=2E
=3E=20
=3E The estimates seem pretty good to me - 10x difference is not that much=
=2E
=3E Could be better=2C but I don=27t think you can get a better plan=2C is=
 seems
=3E very reasonable to me=2E
=3E=20
=3E =3E So=2C any pointers for performance improvement=3F
=3E=20
=3E Three ideas what might help
=3E=20
=3E 1=29 partial indexes
=3E=20
=3E How much do the parameters in the query change=3F If there are paramete=
rs
=3E that are always the same=2C you may try to create partial indexes=2E Fo=
r
=3E example if the =27vdd=2Evolume=27 always has to be =270=27=2C then you=
 can create
=3E the index like this
=3E=20
=3E  CREATE INDEX vds=5Fdetector=5Fdata=5Fdir=5Fidx
=3E    ON vds=5Fdetector=5Fdata
=3E    USING btree
=3E    =28road=5Fdir=29
=3E    WHERE =28vdd=2Evolume!=3D0=29=3B
=3E=20
=3E That way only the rows with =27vdd=2Evolume!=3D0=27 will be included in=
 the
=3E index=2C the index will be smaller and the bitmap will be created faste=
r=2E
=3E Similarly for the other conditions=2E The smaller the index will be=2C=
 the
=3E faster the bitmap creation=2E
=3E=20
=3E If all the conditions may change=2C or if the index size does not chang=
e
=3E much=2C you can=27t do this=2E

The 0 volume is the only thing that will always be present=2C but those rec=
ords do account for 10 to 15=25 of the data=2E  I=27ll give this a shot=2C=
 I=27m really interested in seeing what impact this had=2E  For some reason=
 I was under the impression that partial indexes were used for text searche=
s=2C so I completely overlooked this=2E

=3E=20
=3E 2=29 prebuilt results
=3E=20
=3E Another option is precomputation of the =27per partition results=27 - i=
f you
=3E know what the conditions are going to be=2C you can precompute them and=

=3E then query just those =28much smaller=29 tables=2E But this is very
=3E application specific=2E
=3E=20
=3E Again=2C if the all the conditions change=2C you can=27t do this=2E

This has been one of the toughest issues=2E  Due to the filtering capabilit=
ies=2C it=27s just not possible to precalculate anything=2E

=3E=20
=3E 3=29 distribute the queries
=3E=20
=3E As I=27ve mentioned=2C PostgreSQL does not distribute the queries on
=3E multiple CPU cores=2C but you can do that on your own at the applicatio=
n
=3E level=2E
=3E=20
=3E For example I see the GROUP BY clause contains =27date=2C hour=2C min=
=27 so you
=3E can compute the results for each partition separately =28in a different=

=3E thread=2C using a separate connection=29 and then =27append=27 them=2E=

=3E=20
=3E Yes=2C you=27ll need to keep some metadata to do this efficiently =28e=
=2Eg=2E list
=3E of partitions along with from/to timestamps=29=2C but you should do thi=
s
=3E anyway I guess =28at least I do that when partitioning tables=2C it mak=
es
=3E the management much easier=29=2E

I noticed this too after a little more testing=2C there are some serious pe=
rformance gains to be had here=2E  I started with a single day query and it=
 took about 15 seconds=2E  Next was 5 simultaneous queries all at about 30=
 seconds each and then 10 queries at 50 seconds each=2E

=3E=20
=3E Not sure if you can do this with the other queries =3A-=28

Not all=2C but many=2E  The query in question is the most beastly of them a=
ll=2C so I=27m pretty happy to have some strategy for improvement=2E

=3E=20
=3E regards
=3E Tomas
=3E=20
=3E --=20
=3E Sent via pgsql-performance mailing list =28pgsql-performance=40postgres=
ql=2Eorg=29
=3E To make changes to your subscription=3A
=3E http=3A//www=2Epostgresql=2Eorg/mailpref/pgsql-performance
=3E=20

Thanks for the help=2E
Craig=


This e-mail communication =28including any attachments=29 may contain confi=
dential and/or privileged material intended solely for the individual or en=
tity to which it is addressed=2E
P - Think before you print=2E

------------MIME--2118040468-41-delim
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

=3Chtml=3E
=3Cbody=3E
=26gt=3B Dne 28=2E6=2E2011 23=3A28=2C Craig McIlwee napsal=28a=29=3A=3Cbr=
=3E=26gt=3B =26gt=3B Daily table explain analyze=3A http=3A//explain=2Edepe=
sz=2Ecom/s/iLY=3Cbr=3E=26gt=3B =26gt=3B Half month table explain analyze=3A=
 http=3A//explain=2Edepesz=2Ecom/s/Unt=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B Are=
 you sure those two queries are exactly the same=3F Because the daily=3Cbr=
=3E=26gt=3B case output says the width is 50B=2C while the half-month case=
 says it=27s=3Cbr=3E=26gt=3B 75B=2E This might be why the sort/aggregate st=
eps are switched=2C and that=3Cbr=3E=26gt=3B increases the amount of data s=
o that it has to be sorted on disk =28which=3Cbr=3E=26gt=3B is why the half=
-month is so much slower=29=2E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B Haven=27t y=
ou added some columns to the half-month query=3F=3Cbr=3E=3Cbr=3EThe daily t=
ables were created using CREATE TABLE AS from the half month tables=2C stru=
cture is the same with the exception of fill factor=2E=26nbsp=3B Queries ar=
e identical except for the name of the master table that they select from=
=2E=3Cbr=3E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B =26gt=3B Postgres version=3A=
=3Cbr=3E=26gt=3B =26gt=3B PostgreSQL 8=2E4=2E8=2C compiled by Visual C++ bu=
ild 1400=2C 32-bit=3Cbr=3E=26gt=3B =26gt=3B =3Cbr=3E=26gt=3B =26gt=3B Histo=
ry=3A=3Cbr=3E=26gt=3B =26gt=3B None=2C this is a new database and applicati=
on=3Cbr=3E=26gt=3B =26gt=3B =3Cbr=3E=26gt=3B =26gt=3B Hardware=3A=3Cbr=3E=
=26gt=3B =26gt=3B - 2 Intel Xeon 2=2E13GHz processors with 8 cores each=3Cb=
r=3E=26gt=3B =26gt=3B - 8GB RAM=3Cbr=3E=26gt=3B =26gt=3B - Disks are in a R=
AID 5 configuration with a PERC H700 Integrated RAID=3Cbr=3E=26gt=3B =26gt=
=3B Controller 512MB Cache=3Cbr=3E=26gt=3B =26gt=3B - 5 disks=2C Seagate 72=
00 RPM SAS=2C 500GB each for a total capacity of=3Cbr=3E=26gt=3B =26gt=3B a=
bout 2TB=3Cbr=3E=26gt=3B =26gt=3B - Windows Server 2008 R2 64-bit =28but 32=
-bit postgres=29=3Cbr=3E=26gt=3B =26gt=3B - Hardware upgrades arent an opti=
on at this point due to budget and time=3Cbr=3E=26gt=3B =26gt=3B constraint=
s=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B Not much experience with PostgreSQL on W=
indows=2C but this looks good to=3Cbr=3E=26gt=3B me=2E Not sure if RAID5 is=
 a good choice=2C especially because of write=3Cbr=3E=26gt=3B performance -=
 this is probably one of the reasons why the disk sort is=3Cbr=3E=26gt=3B s=
o slow =28in the half-month case=29=2E=3Cbr=3E=3Cbr=3EYes=2C the data impor=
t is painfully slow but I hope to make up for that with the read performanc=
e later=2E=3Cbr=3E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B And it=27s nice you hav=
e 8 cores=2C but don=27t forget each query executes on=3Cbr=3E=26gt=3B a si=
ngle background process=2C i=2Ee=2E it may use single core=2E So the above=
=3Cbr=3E=26gt=3B query can=27t use 8 cores - that=27s why the in-memory sor=
t takes so long I=3Cbr=3E=26gt=3B guess=2E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B=
 =26gt=3B Maintenance Setup=3A=3Cbr=3E=26gt=3B =26gt=3B Autovacuum is disab=
led for these tables since the data is never=3Cbr=3E=26gt=3B =26gt=3B updat=
ed=2E=26nbsp=3B The tables that we are testing with at the moment will not=
=3Cbr=3E=26gt=3B =26gt=3B grow any larger and have been both clustered and=
 analyzed=2E=26nbsp=3B They were=3Cbr=3E=26gt=3B =26gt=3B clustered on the=
 vds=5Fdetector=5Fdata=5Ftimestamp=5Fidx index=2E=3Cbr=3E=26gt=3B =26gt=3B=
 =3Cbr=3E=26gt=3B =26gt=3B GUC Settings=3A=3Cbr=3E=26gt=3B =26gt=3B effecti=
ve=5Fcache=5Fsize=3A 2048MB=3Cbr=3E=26gt=3B =26gt=3B work=5Fmem=3A 512MB=3C=
br=3E=26gt=3B =26gt=3B shared=5Fbuffers=3A 64MB=2C 512MB=2C and 1024MB=2C e=
ach yielded the same query=3Cbr=3E=26gt=3B =26gt=3B plan and took the same=
 amount of time to execute give or take a few=3Cbr=3E=26gt=3B seconds=3Cbr=
=3E=26gt=3B =26gt=3B =3Cbr=3E=26gt=3B =26gt=3B Summary=3A=3Cbr=3E=26gt=3B=
 =26gt=3B =3Cbr=3E=26gt=3B =26gt=3B The time to get the raw data =28before=
 aggregation and sorting=29 is=3Cbr=3E=26gt=3B =26gt=3B relatively similar=
 between the daily and half month tables=2E=26nbsp=3B It would=3Cbr=3E=26gt=
=3B =26gt=3B appear that the major difference is the ordering of sort and=
=3Cbr=3E=26gt=3B =26gt=3B aggregation=2C the daily tables aggregate first s=
o the amount of data=3Cbr=3E=26gt=3B =26gt=3B sorted is significantly less=
=2E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B Yes=2C the ordering is the problem=2E=
 The amount of data to sort is so huge=3Cbr=3E=26gt=3B =283GB=29 it does no=
t fit into work=5Fmem and has to be sorted on disk=2E Not=3Cbr=3E=26gt=3B s=
ure why this happens=2C the only difference I=27ve noticed is the =27width=
=27=3Cbr=3E=26gt=3B =2850B vs=2E 75B=29=2E Are those two queries exactly th=
e same=3F=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B =26gt=3B Since the daily tables=
 are only 360MB=2C I would hope that the entire=3Cbr=3E=26gt=3B =26gt=3B ta=
ble could be pulled into memory with one large sequential read=2E=26nbsp=3B=
 Of=3Cbr=3E=26gt=3B =26gt=3B course this assumes that the file pieces are s=
tored contiguously=2C but=3Cbr=3E=26gt=3B =26gt=3B auto defrag is enabled a=
nd shows low fragmentation so Im trusting =28as=3Cbr=3E=26gt=3B =26gt=3B mu=
ch as one can=29 Windows to do the right thing here=2E=26nbsp=3B My drives=
 have a=3Cbr=3E=26gt=3B =26gt=3B 150MB/s sustained max throughput=2C and co=
nsidering that data is spread=3Cbr=3E=26gt=3B =26gt=3B across 5 drives I wo=
uld hope to at least be able to reach the single=3Cbr=3E=26gt=3B =26gt=3B d=
isk theoretical limit and read an entire table plus the index into=3Cbr=3E=
=26gt=3B =26gt=3B memory about 4 to 5 seconds=2E=26nbsp=3B Based on the ana=
lyze output=2C each daily=3Cbr=3E=26gt=3B =26gt=3B table averages 6 to 7 se=
conds=2C so Im pretty close there and maybe just=3Cbr=3E=26gt=3B =26gt=3B l=
imited by disk speed=3F=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B Well=2C you have 3=
0 partitions and 7 seconds for each means 210 secons in=3Cbr=3E=26gt=3B tot=
al=2E Which is about the time you get =28before the aggregate/sort=29=2E=3C=
br=3E=26gt=3B =3Cbr=3E=26gt=3B You have to check where the bottleneck is -=
 is it the I/O or CPU=3F I=27d=3Cbr=3E=26gt=3B guess the CPU=2C but I may b=
e wrong=2E On unix I=27d use something like=3Cbr=3E=26gt=3B iostat/vmstat/t=
op to see what=27s going on - not sure what to use on=3Cbr=3E=26gt=3B Windo=
ws=2E I guess there is a some console or maybe Process Explorer from=3Cbr=
=3E=26gt=3B sysinternals=2E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B =26gt=3B In bo=
th cases=2C the row estimates vs actual are way off=2E=26nbsp=3B Ive increa=
sed=3Cbr=3E=26gt=3B =26gt=3B statistics on the reading=5Ftimestamp and road=
=5Fname columns to 100 and=3Cbr=3E=26gt=3B =26gt=3B then 1000 with no chang=
e=2E=26nbsp=3B I ran an ANALYZE after each statistics=3Cbr=3E=26gt=3B =26gt=
=3B change=2E=26nbsp=3B Should I be upping stats on the non-indexed columns=
 as well=3F =3Cbr=3E=26gt=3B =26gt=3B Ive read documentation that says I sh=
ould be able to set statistics=3Cbr=3E=26gt=3B =26gt=3B values for an entir=
e table as opposed to per column=2C but havent found=3Cbr=3E=26gt=3B =26gt=
=3B how to do that=2E=26nbsp=3B I guess I was either too lazy to update sta=
tistics on=3Cbr=3E=26gt=3B =26gt=3B each column or just didnt think it woul=
d help much=2E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B The estimates seem pretty g=
ood to me - 10x difference is not that much=2E=3Cbr=3E=26gt=3B Could be bet=
ter=2C but I don=27t think you can get a better plan=2C is seems=3Cbr=3E=26=
gt=3B very reasonable to me=2E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B =26gt=3B So=
=2C any pointers for performance improvement=3F=3Cbr=3E=26gt=3B =3Cbr=3E=26=
gt=3B Three ideas what might help=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B 1=29 par=
tial indexes=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B How much do the parameters in=
 the query change=3F If there are parameters=3Cbr=3E=26gt=3B that are alway=
s the same=2C you may try to create partial indexes=2E For=3Cbr=3E=26gt=3B=
 example if the =27vdd=2Evolume=27 always has to be =270=27=2C then you can=
 create=3Cbr=3E=26gt=3B the index like this=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=
=3B=26nbsp=3B CREATE INDEX vds=5Fdetector=5Fdata=5Fdir=5Fidx=3Cbr=3E=26gt=
=3B=26nbsp=3B=26nbsp=3B=26nbsp=3B ON vds=5Fdetector=5Fdata=3Cbr=3E=26gt=3B=
=26nbsp=3B=26nbsp=3B=26nbsp=3B USING btree=3Cbr=3E=26gt=3B=26nbsp=3B=26nbsp=
=3B=26nbsp=3B =28road=5Fdir=29=3Cbr=3E=26gt=3B=26nbsp=3B=26nbsp=3B=26nbsp=
=3B WHERE =28vdd=2Evolume!=3D0=29=3B=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B That=
 way only the rows with =27vdd=2Evolume!=3D0=27 will be included in the=3Cb=
r=3E=26gt=3B index=2C the index will be smaller and the bitmap will be crea=
ted faster=2E=3Cbr=3E=26gt=3B Similarly for the other conditions=2E The sma=
ller the index will be=2C the=3Cbr=3E=26gt=3B faster the bitmap creation=2E=
=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B If all the conditions may change=2C or if=
 the index size does not change=3Cbr=3E=26gt=3B much=2C you can=27t do this=
=2E=3Cbr=3E=3Cbr=3EThe 0 volume is the only thing that will always be prese=
nt=2C but those records do account for 10 to 15=25 of the data=2E=26nbsp=3B=
 I=27ll give this a shot=2C I=27m really interested in seeing what impact t=
his had=2E=26nbsp=3B For some reason I was under the impression that partia=
l indexes were used for text searches=2C so I completely overlooked this=2E=
=3Cbr=3E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B 2=29 prebuilt results=3Cbr=3E=26g=
t=3B =3Cbr=3E=26gt=3B Another option is precomputation of the =27per partit=
ion results=27 - if you=3Cbr=3E=26gt=3B know what the conditions are going=
 to be=2C you can precompute them and=3Cbr=3E=26gt=3B then query just those=
 =28much smaller=29 tables=2E But this is very=3Cbr=3E=26gt=3B application=
 specific=2E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B Again=2C if the all the condi=
tions change=2C you can=27t do this=2E=3Cbr=3E=3Cbr=3EThis has been one of=
 the toughest issues=2E=26nbsp=3B Due to the filtering capabilities=2C it=
=27s just not possible to precalculate anything=2E=3Cbr=3E=3Cbr=3E=26gt=3B=
 =3Cbr=3E=26gt=3B 3=29 distribute the queries=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=
=3B As I=27ve mentioned=2C PostgreSQL does not distribute the queries on=3C=
br=3E=26gt=3B multiple CPU cores=2C but you can do that on your own at the=
 application=3Cbr=3E=26gt=3B level=2E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B For=
 example I see the GROUP BY clause contains =27date=2C hour=2C min=27 so yo=
u=3Cbr=3E=26gt=3B can compute the results for each partition separately=20=
=28in a different=3Cbr=3E=26gt=3B thread=2C using a separate connection=29=
 and then =27append=27 them=2E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B Yes=2C you=
=27ll need to keep some metadata to do this efficiently =28e=2Eg=2E list=3C=
br=3E=26gt=3B of partitions along with from/to timestamps=29=2C but you sho=
uld do this=3Cbr=3E=26gt=3B anyway I guess =28at least I do that when parti=
tioning tables=2C it makes=3Cbr=3E=26gt=3B the management much easier=29=2E=
=3Cbr=3E=3Cbr=3EI noticed this too after a little more testing=2C there are=
 some serious performance gains to be had here=2E=26nbsp=3B I started with=
 a single day query and it took about 15 seconds=2E=26nbsp=3B Next was 5 si=
multaneous queries all at about 30 seconds each and then 10 queries at 50 s=
econds each=2E=3Cbr=3E=3Cbr=3E=26gt=3B =3Cbr=3E=26gt=3B Not sure if you can=
 do this with the other queries =3A-=28=3Cbr=3E=3Cbr=3ENot all=2C but many=
=2E=26nbsp=3B The query in question is the most beastly of them all=2C so I=
=27m pretty happy to have some strategy for improvement=2E=3Cbr=3E=3Cbr=3E=
=26gt=3B =3Cbr=3E=26gt=3B regards=3Cbr=3E=26gt=3B Tomas=3Cbr=3E=26gt=3B=20=
=3Cbr=3E=26gt=3B -- =3Cbr=3E=26gt=3B Sent via pgsql-performance mailing lis=
t =28pgsql-performance=40postgresql=2Eorg=29=3Cbr=3E=26gt=3B To make change=
s to your subscription=3A=3Cbr=3E=26gt=3B http=3A//www=2Epostgresql=2Eorg/m=
ailpref/pgsql-performance=3Cbr=3E=26gt=3B =3Cbr=3E=3Cbr=3EThanks for the he=
lp=2E=3Cbr=3ECraig=3CBR /=3E
=3CBR /=3E
=3Cspan style=3D=22font-family=3A =27calibri=27=2C=27sans-serif=27=3B font-=
size=3A 8pt=3B mso-ascii-theme-font=3A minor-latin=3B mso-fareast-font-fami=
ly=3A =27times new roman=27=3B mso-fareast-theme-font=3A minor-fareast=3B m=
so-hansi-theme-font=3A minor-latin=3B mso-bidi-font-family=3A =27times new=
 roman=27=3B mso-bidi-theme-font=3A minor-bidi=3B mso-ansi-language=3A en-u=
s=3B mso-fareast-language=3A en-us=3B mso-bidi-language=3A ar-sa=3B mso-no-=
proof=3A yes=3B=22=3E=3Ci=3EThis e-mail communication =28including any atta=
chments=29 may contain confidential and/or privileged material intended sol=
ely for the individual or entity to which it is addressed=2E=3Cbr=3E=3C/i=
=3E=3C/span=3E=3Cb=3E=3Cspan style=3D=22font-family=3A webdings=3B color=3A=
 =23003300=3B font-size=3A 18pt=3B mso-fareast-font-family=3A =27times new=
 roman=27=3B mso-fareast-theme-font=3A minor-fareast=3B mso-bidi-font-famil=
y=3A =27times new roman=27=3B mso-bidi-theme-font=3A minor-bidi=3B mso-ansi=
-language=3A en-us=3B mso-fareast-language=3A en-us=3B mso-bidi-language=3A=
 ar-sa=3B mso-no-proof=3A yes=3B=22=3EP=3C/span=3E=3Cspan style=3D=22font-f=
amily=3A =27arial=27=2C=27sans-serif=27=3B color=3A =23003300=3B font-size=
=3A 8pt=3B mso-fareast-font-family=3A =27times new roman=27=3B mso-fareast-=
theme-font=3A minor-fareast=3B mso-ansi-language=3A en-us=3B mso-fareast-la=
nguage=3A en-us=3B mso-bidi-language=3A ar-sa=3B mso-no-proof=3A yes=3B=22=
=3E - Think before you print=3C/span=3E=3Cspan style=3D=22font-family=3A=20=
=27arial=27=2C=27sans-serif=27=3B color=3A =23003300=3B font-size=3A 10pt=
=3B mso-fareast-font-family=3A =27times new roman=27=3B mso-fareast-theme-f=
ont=3A minor-fareast=3B mso-ansi-language=3A en-us=3B mso-fareast-language=
=3A en-us=3B mso-bidi-language=3A ar-sa=3B mso-no-proof=3A yes=3B=22=3E=2E=
=3C/span=3E=3C/b=3E=3CBR /=3E
=3C/body=3E
=3C/html=3E


------------MIME--2118040468-41-delim--