Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

www.xxxcon, which comes first ob_start or session, wwwxxx/58/2010, xxxxdup, xxxxdup, mailx informatii, should producers of software-based services, such as atms, be held liable for economic injuries suffered when their systems fail?, mysql feiertage, bash netcat eof, 192.168.1.41:8000

Links

XODOX
Impressum

#1: Performance bricht abrupt ein bei großen Ergebnismengen

Posted on 2011-09-29 17:32:10 by Volker Sievert

Hi Leute,

wahrscheinlich habe ich irgend etwas grundlegendes =FCbersehen, aber=20
vielleicht kann mir jemand auf die Spr=FCnge helfen.

Ich hab hier eine Datenbank mit einigen recht gro=DFen Tabellen (einige=20
zig Millionen Eintr=E4ge) und ben=F6tige Abfragen, die recht recht gro=DF=
e=20
Ergebnismengen liefern werden (einige 100000 bis Millionen Eintr=E4ge),=20
zudem m=F6chte ich Views =FCber diese Tabellen zur Verf=FCgung stellen. D=
as=20
funktioniert bis zu einer gewissen GröÃe ziemlich passabel, aber es=20
scheint eine ziemlich harte Grenze zu geben, bei der die Geschwindigkeit=20
abrupt einbricht.

Warum ist das so und was kann man dagegen tun?

Ein Beispiel. Zwei Tabellen, wobei die erste mehrere Fremdschl=FCssel auf=
=20
die zweite hat:

Tabelle 1 (derzeit ca. 1,4 Millionen Eintr=E4ge):

CREATE TABLE vquest_aa
(
id bigint NOT NULL DEFAULT nextval('vquest_aa_id_seq'::regclass),
sequence_id character varying(80) NOT NULL,
vdj bigint,
vregion bigint,
fr1_imgt bigint,

-- some fields omitted for clarity...

entrydate timestamp without time zone NOT NULL DEFAULT now(),

CONSTRAINT vquest_aa_pkey PRIMARY KEY (id),


CONSTRAINT vquest_aa_fr1_imgt_fkey FOREIGN KEY (fr1_imgt)
REFERENCES sequences (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,

--more constraints omitted for clarity

CONSTRAINT vquest_aa_vdj_fkey FOREIGN KEY (vdj)
REFERENCES sequences (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT vquest_aa_vregion_fkey FOREIGN KEY (vregion)
REFERENCES sequences (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT vquest_aa_sequence_id_key UNIQUE (sequence_id)
),

Tabelle 2 (derzeit ca. 22 Millionen Eintr=E4ge):

CREATE TABLE sequences
(
id bigserial NOT NULL,
entrydate timestamp with time zone DEFAULT now(),

"type" character varying(3) NOT NULL DEFAULT 'DNA'::character varying,
seq character varying(10485759) NOT NULL,

CONSTRAINT biosequences_pkey PRIMARY KEY (id),

CONSTRAINT biosequences_type_check CHECK (type::text =3D 'DNA'::text O=
R=20
type::text =3D 'PEP'::text)
)
WITH (
OIDS=3DFALSE
);


Eine Query =FCber diese beiden Tabellen, man beachte das "Limit XXX":

with aa as (select vquest_aa.sequence_id, vdj,
vregion, fr1_imgt (...omitted...)
from vquest_aa limit XXX) select
sequence_id, s1.seq as vdj_seq, s2.seq as vregion_seq,
s3.seq as fr1_seq (...omitted...)
from aa left outer join sequences as s1 on s1.id =3D vdj
left outer join sequences as s2 on s2.id =3D vregion
left outer join sequences as s3 on s3.id =3D fr1_imgt
(...omitted...)

Wenn man das mit verschiedenen Werten f=FCr Limit XXX ausf=FChrt kommt ma=
n=20
zu folgenden Laufzeiten:
---------------------------
result set size (by Limit) query execution time in ms rows per ms
=09
1 32 0,03
10 31 0,32
100 31 3,23
500 78 6,41
1000 141 7,09
10000 1656 6,04
20000 5327 3,75
50000 9593 5,21
100000 14779 6,77
110000 18232 6,03
115000 19784 5,81
117000 22597 5,18
118000 89717 1,32
119000 70435 1,69
119500 179472 0,67
120000 cancelled after 300000ms n.d.
200000 cancelled after 300000ms n.d.
-----------------------------

Irgendwo bei 120000 bricht die Geschwindigkeit dramatisch ein, das=20
passiert je nach Komplexit=E4t der Abfragen bei unterschiedlichen=20
"Grenzwerten" und auch dann, wenn man ein praxisn=E4heres Auswahlkriteriu=
m=20
benutzt als "Limit".

OK, woran liegt dieses Verhalten, was kann man dagegen tun?

Bin f=FCr jeden Tipp dankbar.

Viele GrüÃe, V. Sievert

--=20
Volker Sievert
AG Konthur
Max Planck Institute for Molecular Genetics
Department of Vertebrate Genomics (Prof. H. Lehrach)
Ihnestr.63, 14195 Berlin, Germany
Tel.: +49/30/ 8413-1578
Fax.: +49/30/ 8413-1718 or -1365
sievert@molgen.mpg.de
http://www.molgen.mpg.de/~in-vitro/






--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Report this message

#2: Re: Pe

Posted on 2011-09-29 17:58:59 by Andreas Kretschmer

Volker Sievert <sievert@molgen.mpg.de> wrote:

> Hi Leute,
>
> wahrscheinlich habe ich irgend etwas grundlegendes =FCbersehen, aber =20

Ja, die Ausgabe von EXPLAIN ANALYSE <your query>.


Andreas
--=20
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082=B0, E 13.56889=
=B0

--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Report this message

#3: Re: [pgsql-de-allgemein] Performance bricht abrupt ein bei großen Ergebnismengen

Posted on 2011-09-29 18:11:15 by bjoernhaeuser

Am 29.09.2011 17:58, schrieb Andreas Kretschmer:
> Volker Sievert<sievert@molgen.mpg.de> wrote:
>
>> Hi Leute,
>>
>> wahrscheinlich habe ich irgend etwas grundlegendes =FCbersehen, aber
>
> Ja, die Ausgabe von EXPLAIN ANALYSE<your query>.
>
>
> Andreas

Aus dem Bauch heraus w=FCrde ich sch=E4tzen, dass ziemlich genau bei der=20
Schwelle der RAM ausgeht.
Du k=F6nntest mal die Ausgaben der verschiedenen Tools (htop, frem,=20
vmstat, etc.) w=E4hrend dem Query periodisch =FCberpr=FCfen.
Bei gro=DFen Datenmengen kann das Sortieren recht viel Zeit brauchen.

Aber nat=FCrlich hilft ein EXPLAIN ANALYZE am besten ;)

Bj=F6rn

--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Report this message

#4: Re: Re

Posted on 2011-09-29 18:55:11 by Andreas Kretschmer

Bj=F6rn H=E4user <bjoernhaeuser@gmail.com> wrote:

>
>
> Am 29.09.2011 17:58, schrieb Andreas Kretschmer:
>> Volker Sievert<sievert@molgen.mpg.de> wrote:
>>
>>> Hi Leute,
>>>
>>> wahrscheinlich habe ich irgend etwas grundlegendes =FCbersehen, aber
>>
>> Ja, die Ausgabe von EXPLAIN ANALYSE<your query>.
>>
>>
>> Andreas
>
> Aus dem Bauch heraus w=FCrde ich sch=E4tzen, dass ziemlich genau bei de=
r =20
> Schwelle der RAM ausgeht.

Jepp. Oder der Plan sich =E4ndert. Oder beides.


> Du k=F6nntest mal die Ausgaben der verschiedenen Tools (htop, frem, =20
> vmstat, etc.) w=E4hrend dem Query periodisch =FCberpr=FCfen.
> Bei gro=DFen Datenmengen kann das Sortieren recht viel Zeit brauchen.

Einen ORDER BY habsch gar ned entdeckt ...


>
> Aber nat=FCrlich hilft ein EXPLAIN ANALYZE am besten ;)

Ack. Alles andere ist Kaffeesatz lesen...



Andreas
--=20
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082=B0, E 13.56889=
=B0

--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Report this message

#5: Re: [pgsql-de-allgemein] Performance bricht abrupt ein bei großen Ergebnismengen

Posted on 2011-09-30 07:43:22 by hs

hallo,

ich denke, dir kippt der plan irgendwo um. das kann man ansich schon ganz g=
ut in den griff bekommen, denke ich (stichwort planner cost parameter und c=
o).
eine ferndiagnose ist aber schwer, wenn die query nicht vollst=E4ndig ist.
wie meine kollegen schon gesagt haben; poste mal mit verschiedenen datenmen=
gen ein ...

explain (buffers true, costs true, analyze true) ... (sofern deine version =
von pg schon mehr als nur plain "explain analyze" kann.

was mir noch aufgefallen ist: "from vquest_aa limit XXX".
das wird dir ein ziemlich zuf=E4lliges ergebnis geben ... das ergebnis ist =
ziemlich vom disk layout abh=E4ngig. will man das?

meine wette: der plan f=FCr die joins kippt um ...

liebe grüÃe,

hans


On Sep 29, 2011, at 5:32 PM, Volker Sievert wrote:

> Hi Leute,
>=20
> wahrscheinlich habe ich irgend etwas grundlegendes =FCbersehen, aber viel=
leicht kann mir jemand auf die Spr=FCnge helfen.
>=20
> Ich hab hier eine Datenbank mit einigen recht gro=DFen Tabellen (einige z=
ig Millionen Eintr=E4ge) und ben=F6tige Abfragen, die recht recht gro=DFe E=
rgebnismengen liefern werden (einige 100000 bis Millionen Eintr=E4ge), zude=
m m=F6chte ich Views =FCber diese Tabellen zur Verf=FCgung stellen. Das fun=
ktioniert bis zu einer gewissen GröÃe ziemlich passabel, aber es schein=
t eine ziemlich harte Grenze zu geben, bei der die Geschwindigkeit abrupt e=
inbricht.
>=20
> Warum ist das so und was kann man dagegen tun?
>=20
> Ein Beispiel. Zwei Tabellen, wobei die erste mehrere Fremdschl=FCssel auf=
die zweite hat:
>=20
> Tabelle 1 (derzeit ca. 1,4 Millionen Eintr=E4ge):
>=20
> CREATE TABLE vquest_aa
> (
> id bigint NOT NULL DEFAULT nextval('vquest_aa_id_seq'::regclass),
> sequence_id character varying(80) NOT NULL,
> vdj bigint,
> vregion bigint,
> fr1_imgt bigint,
>=20
> -- some fields omitted for clarity...
>=20
> entrydate timestamp without time zone NOT NULL DEFAULT now(),
>=20
> CONSTRAINT vquest_aa_pkey PRIMARY KEY (id),
>=20
>=20
> CONSTRAINT vquest_aa_fr1_imgt_fkey FOREIGN KEY (fr1_imgt)
> REFERENCES sequences (id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE CASCADE,
>=20
> --more constraints omitted for clarity
>=20
> CONSTRAINT vquest_aa_vdj_fkey FOREIGN KEY (vdj)
> REFERENCES sequences (id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE CASCADE,
> CONSTRAINT vquest_aa_vregion_fkey FOREIGN KEY (vregion)
> REFERENCES sequences (id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE CASCADE,
> CONSTRAINT vquest_aa_sequence_id_key UNIQUE (sequence_id)
> ),
>=20
> Tabelle 2 (derzeit ca. 22 Millionen Eintr=E4ge):
>=20
> CREATE TABLE sequences
> (
> id bigserial NOT NULL,
> entrydate timestamp with time zone DEFAULT now(),
>=20
> "type" character varying(3) NOT NULL DEFAULT 'DNA'::character varying,
> seq character varying(10485759) NOT NULL,
>=20
> CONSTRAINT biosequences_pkey PRIMARY KEY (id),
>=20
> CONSTRAINT biosequences_type_check CHECK (type::text =3D 'DNA'::text OR =
type::text =3D 'PEP'::text)
> )
> WITH (
> OIDS=3DFALSE
> );
>=20
>=20
> Eine Query =FCber diese beiden Tabellen, man beachte das "Limit XXX":
>=20
> with aa as (select vquest_aa.sequence_id, vdj,
> vregion, fr1_imgt (...omitted...)
> from vquest_aa limit XXX) select
> sequence_id, s1.seq as vdj_seq, s2.seq as vregion_seq,
> s3.seq as fr1_seq (...omitted...)
> from aa left outer join sequences as s1 on s1.id =3D vdj
> left outer join sequences as s2 on s2.id =3D vregion
> left outer join sequences as s3 on s3.id =3D fr1_imgt
> (...omitted...)
>=20
> Wenn man das mit verschiedenen Werten f=FCr Limit XXX ausf=FChrt kommt ma=
n zu folgenden Laufzeiten:
> ---------------------------
> result set size (by Limit) query execution time in ms rows per ms
> =09
> 1 32 0,03
> 10 31 0,32
> 100 31 3,23
> 500 78 6,41
> 1000 141 7,09
> 10000 1656 6,04
> 20000 5327 3,75
> 50000 9593 5,21
> 100000 14779 6,77
> 110000 18232 6,03
> 115000 19784 5,81
> 117000 22597 5,18
> 118000 89717 1,32
> 119000 70435 1,69
> 119500 179472 0,67
> 120000 cancelled after 300000ms n.d.
> 200000 cancelled after 300000ms n.d.
> -----------------------------
>=20
> Irgendwo bei 120000 bricht die Geschwindigkeit dramatisch ein, das passie=
rt je nach Komplexit=E4t der Abfragen bei unterschiedlichen "Grenzwerten" u=
nd auch dann, wenn man ein praxisn=E4heres Auswahlkriterium benutzt als "Li=
mit".
>=20
> OK, woran liegt dieses Verhalten, was kann man dagegen tun?
>=20
> Bin f=FCr jeden Tipp dankbar.
>=20
> Viele GrüÃe, V. Sievert
>=20
> --=20
> Volker Sievert
> AG Konthur
> Max Planck Institute for Molecular Genetics
> Department of Vertebrate Genomics (Prof. H. Lehrach)
> Ihnestr.63, 14195 Berlin, Germany
> Tel.: +49/30/ 8413-1578
> Fax.: +49/30/ 8413-1718 or -1365
> sievert@molgen.mpg.de
> http://www.molgen.mpg.de/~in-vitro/
>=20
>=20
>=20
>=20
>=20
>=20
> --=20
> Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.o=
rg)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-de-allgemein
>=20


--
Cybertec Sch=F6nig & Sch=F6nig GmbH
Gr=F6hrm=FChlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de


--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Report this message

#6: Re: [pgsql-de-allgemein] Performance bricht abrupt ein bei großen Ergebnismengen

Posted on 2011-09-30 12:55:52 by Susanne Ebrecht

Hallo,

On 29.09.2011 17:32, Volker Sievert wrote:
>
> Eine Query =FCber diese beiden Tabellen, man beachte das "Limit XXX":


Ich kann mich meinen Vorrednern nur anschlie=DFen.

Eine Frage habe ich aber noch, wo ich LIMIT sehe:
Welche PostgreSQL Version ist es?

Susanne

--=20
Susanne Ebrecht - 2ndQuadrant
PostgreSQL Development, 24x7 Support, Training and Services
www.2ndQuadrant.com


--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Report this message

#7: Re: [pgsql-de-allgemein] Performance bricht abrupt ein bei großen Ergebnismengen

Posted on 2011-09-30 15:28:35 by Volker Sievert

On 30.09.2011 07:43, Hans-J=FCrgen Sch=F6nig wrote:
> hallo,
>
> ich denke, dir kippt der plan irgendwo um. das kann man ansich schon ga=
nz gut in den griff bekommen, denke ich (stichwort planner cost parameter=
und co).
> eine ferndiagnose ist aber schwer, wenn die query nicht vollst=E4ndig i=
st.
> wie meine kollegen schon gesagt haben; poste mal mit verschiedenen date=
nmengen ein ...
>
> explain (buffers true, costs true, analyze true) ... (sofern deine vers=
ion von pg schon mehr als nur plain "explain analyze" kann.

Vielen Dank f=FCr die Hinweise,

ich muss zugeben, dass mich die Interpretation des Explain=20
Analyse-Output etwas =FCberfordert (bin Biolge, kein Informatiker...) -=20
versucht hatte ich das schon vorher mal und bin nicht so recht schlau=20
daraus geworden.

Als nicht-Informatiker fallen mir bei der "gro=DFen" Query die vielen=20
Aufrufe von "Materialize" auf - das w=FCrde ich mal so interpretieren,=20
dass da st=E4ndig Zwischenergebnisse auf Platte geschrieben werden - nur,=
=20
was kann man dagegen tun? Mehr Metall werde ich kaum daf=FCr bekommen...

das ganze ist Postgresql Version 9.0 und l=E4uft unter Linux 2.6.36 (X64)=
=20
auf 4 GiB RAM und teilt sich die Resourcen mit einer Java-Anwendung.

Zumindest laut "top" steigt der RAM-Verbrauch aber nicht auf unmäÃig=20
hohe Werte, mehr als 15-20% will Postgres w=E4hrend der Ausf=FChrung dies=
er=20
Querys nicht haben.


L=E4ufe von Explain mit einer real existierenden Query:

----------------------------
explain (buffers true, costs true, analyze true)
with aa as (select vquest_aa.sequence_id, abdb_sequence, vdj,
vregion, fr1_imgt, fr2_imgt, fr3_imgt,
fr4_imgt, cdr1, cdr2, cdr3, jregion, junction
from vquest_aa inner join sequence on vquest_aa.sequence_id =
sequence.sequence_id limit XXX) select
sequence_id, abdb_sequence, s1.seq as vdj_seq, s2.seq as vregion_seq,
s3.seq as fr1_seq, s4.seq as fr2_seq, s5.seq as fr3_seq, s6.seq as fr4_se=
q,
s7.seq as cdr1_seq, s8.seq as cdr2_seq, s9.seq as cdr3_seq,
s10.seq as jregion, s11.seq as junction


from aa left outer join sequences as s1 on s1.id =3D vdj
left outer join sequences as s2 on s2.id =3D vregion
left outer join sequences as s3 on s3.id =3D fr1_imgt
left outer join sequences as s4 on s4.id =3D fr2_imgt
left outer join sequences as s5 on s5.id =3D fr3_imgt
left outer join sequences as s6 on s6.id =3D fr4_imgt
left outer join sequences as s7 on s7.id =3D cdr1
left outer join sequences as s8 on s8.id =3D cdr2
left outer join sequences as s9 on s9.id =3D cdr3
left outer join sequences as s10 on s10.id =3D jregion
left outer join sequences as s11 on s11.id =3D junction
---------------------------------------------------

ergeben folgenden Output bei XXX =3D 200:

---------------------------------------------------
"Nested Loop Left Join (cost=3D1695.94..25780.53 rows=3D200 width=3D1231=
)=20
(actual time=3D55.106..87.102 rows=3D200 loops=3D1)"
" Buffers: shared hit=3D9534 read=3D49"
" CTE aa"
" -> Limit (cost=3D0.00..1695.94 rows=3D200 width=3D104) (actual=20
time=3D43.635..49.006 rows=3D200 loops=3D1)"
" Buffers: shared hit=3D794 read=3D13"
" -> Nested Loop (cost=3D0.00..13051100.46 rows=3D1539098=20
width=3D104) (actual time=3D43.631..48.474 rows=3D200 loops=3D1)"
" Buffers: shared hit=3D794 read=3D13"
" -> Seq Scan on vquest_aa (cost=3D0.00..44881.98=20
rows=3D1539098 width=3D96) (actual time=3D16.268..17.140 rows=3D200 loops=
=3D1)"
" Buffers: shared read=3D4"
" -> Index Scan using sequence_pkey on sequence=20
(cost=3D0.00..8.44 rows=3D1 width=3D16) (actual time=3D0.148..0.150 rows=3D=
1=20
loops=3D200)"
" Index Cond: ((sequence.sequence_id)::text =
(vquest_aa.sequence_id)::text)"
" Buffers: shared hit=3D794 read=3D9"
" -> Nested Loop Left Join (cost=3D0.00..21895.44 rows=3D200 width=3D1=
144)=20
(actual time=3D55.097..84.777 rows=3D200 loops=3D1)"
" Buffers: shared hit=3D8742 read=3D48"
" -> Nested Loop Left Join (cost=3D0.00..19706.30 rows=3D200=20
width=3D1057) (actual time=3D55.088..82.395 rows=3D200 loops=3D1)"
" Buffers: shared hit=3D7950 read=3D47"
" -> Nested Loop Left Join (cost=3D0.00..17517.15 rows=3D2=
00=20
width=3D970) (actual time=3D55.080..80.111 rows=3D200 loops=3D1)"
" Buffers: shared hit=3D7152 read=3D47"
" -> Nested Loop Left Join (cost=3D0.00..15328.01=20
rows=3D200 width=3D883) (actual time=3D55.070..77.807 rows=3D200 loops=3D=
1)"
" Buffers: shared hit=3D6352 read=3D47"
" -> Nested Loop Left Join=20
(cost=3D0.00..13138.86 rows=3D200 width=3D796) (actual time=3D55.062..75.=
404=20
rows=3D200 loops=3D1)"
" Buffers: shared hit=3D5551 read=3D47"
" -> Nested Loop Left Join=20
(cost=3D0.00..10949.72 rows=3D200 width=3D709) (actual time=3D55.054..73.=
134=20
rows=3D200 loops=3D1)"
" Buffers: shared hit=3D4758 read=3D=
47"
" -> Nested Loop Left Join=20
(cost=3D0.00..8760.58 rows=3D200 width=3D622) (actual time=3D55.045..70.8=
40=20
rows=3D200 loops=3D1)"
" Buffers: shared hit=3D3956=20
read=3D47"
" -> Nested Loop Left Join=20
(cost=3D0.00..6571.43 rows=3D200 width=3D535) (actual time=3D55.035..68.5=
38=20
rows=3D200 loops=3D1)"
" Buffers: shared=20
hit=3D3156 read=3D47"
" -> Nested Loop Left=20
Join (cost=3D0.00..4382.29 rows=3D200 width=3D448) (actual=20
time=3D55.026..66.194 rows=3D200 loops=3D1)"
" Buffers: shared=20
hit=3D2354 read=3D47"
" -> Nested Loop=20
Left Join (cost=3D0.00..2193.14 rows=3D200 width=3D361) (actual=20
time=3D55.016..63.817 rows=3D200 loops=3D1)"
" Buffers:=20
shared hit=3D1557 read=3D43"
" -> CTE=20
Scan on aa (cost=3D0.00..4.00 rows=3D200 width=3D274) (actual=20
time=3D43.645..49.767 rows=3D200 loops=3D1)"
"=20
Buffers: shared hit=3D794 read=3D13"
" -> Index=20
Scan using biosequences_pkey on sequences s1 (cost=3D0.00..10.93 rows=3D=
1=20
width=3D103) (actual time=3D0.063..0.064 rows=3D1 loops=3D200)"
"=20
Index Cond: (s1.id =3D aa.vdj)"
"=20
Buffers: shared hit=3D763 read=3D30"
" -> Index Scan=20
using biosequences_pkey on sequences s2 (cost=3D0.00..10.93 rows=3D1=20
width=3D103) (actual time=3D0.004..0.006 rows=3D1 loops=3D200)"
" Index=20
Cond: (s2.id =3D aa.vregion)"
" Buffers:=20
shared hit=3D797 read=3D4"
" -> Index Scan using=20
biosequences_pkey on sequences s3 (cost=3D0.00..10.93 rows=3D1 width=3D1=
03)=20
(actual time=3D0.004..0.006 rows=3D1 loops=3D200)"
" Index Cond:=20
(s3.id =3D aa.fr1_imgt)"
" Buffers: shared=20
hit=3D802"
" -> Index Scan using=20
biosequences_pkey on sequences s4 (cost=3D0.00..10.93 rows=3D1 width=3D1=
03)=20
(actual time=3D0.004..0.005 rows=3D1 loops=3D200)"
" Index Cond: (s4.id =3D=
=20
aa.fr2_imgt)"
" Buffers: shared hit=3D=
800"
" -> Index Scan using=20
biosequences_pkey on sequences s5 (cost=3D0.00..10.93 rows=3D1 width=3D1=
03)=20
(actual time=3D0.004..0.005 rows=3D1 loops=3D200)"
" Index Cond: (s5.id =
aa.fr3_imgt)"
" Buffers: shared hit=3D802"
" -> Index Scan using biosequences_pkey=20
on sequences s6 (cost=3D0.00..10.93 rows=3D1 width=3D103) (actual=20
time=3D0.004..0.005 rows=3D1 loops=3D200)"
" Index Cond: (s6.id =3D aa.fr4_imgt=
)"
" Buffers: shared hit=3D793"
" -> Index Scan using biosequences_pkey on=20
sequences s7 (cost=3D0.00..10.93 rows=3D1 width=3D103) (actual=20
time=3D0.004..0.006 rows=3D1 loops=3D200)"
" Index Cond: (s7.id =3D aa.cdr1)"
" Buffers: shared hit=3D801"
" -> Index Scan using biosequences_pkey on sequences=20
s8 (cost=3D0.00..10.93 rows=3D1 width=3D103) (actual time=3D0.004..0.006=
rows=3D1=20
loops=3D200)"
" Index Cond: (s8.id =3D aa.cdr2)"
" Buffers: shared hit=3D800"
" -> Index Scan using biosequences_pkey on sequences s9=20
(cost=3D0.00..10.93 rows=3D1 width=3D103) (actual time=3D0.004..0.006 row=
s=3D1=20
loops=3D200)"
" Index Cond: (s9.id =3D aa.cdr3)"
" Buffers: shared hit=3D798"
" -> Index Scan using biosequences_pkey on sequences s10=20
(cost=3D0.00..10.93 rows=3D1 width=3D103) (actual time=3D0.004..0.006 row=
s=3D1=20
loops=3D200)"
" Index Cond: (s10.id =3D aa.jregion)"
" Buffers: shared hit=3D792 read=3D1"
" -> Index Scan using biosequences_pkey on sequences s11=20
(cost=3D0.00..10.93 rows=3D1 width=3D103) (actual time=3D0.004..0.006 row=
s=3D1=20
loops=3D200)"
" Index Cond: (s11.id =3D aa.junction)"
" Buffers: shared hit=3D792 read=3D1"
"Total runtime: 87.683 ms"
--------------------------------------------

Und bei XXX=3D200000:

--------------------------------------------
Merge Right Join (cost=3D13165433.17..14401451.91 rows=3D200000 width=3D=
1231)=20
(actual time=3D1865697.697..1939302.226 rows=3D200000 loops=3D1)
Merge Cond: (s11.id =3D aa.junction)
Buffers: shared hit=3D2828843 read=3D7011755, temp written=3D21429
CTE aa
-> Limit (cost=3D86666.71..120575.23 rows=3D200000 width=3D104) (a=
ctual=20
time=3D8463.992..26659.383 rows=3D200000 loops=3D1)
Buffers: shared hit=3D6 read=3D59381, temp written=3D21429
-> Hash Join (cost=3D86666.71..347609.45 rows=3D1539098=20
width=3D104) (actual time=3D8463.988..26136.955 rows=3D200000 loops=3D1)
Hash Cond: ((sequence.sequence_id)::text =
(vquest_aa.sequence_id)::text)
Buffers: shared hit=3D6 read=3D59381, temp written=3D214=
29
-> Seq Scan on sequence (cost=3D0.00..101023.38=20
rows=3D6171438 width=3D16) (actual time=3D5.537..7866.609 rows=3D4693476 =
loops=3D1)
Buffers: shared hit=3D3 read=3D29893
-> Hash (cost=3D44881.98..44881.98 rows=3D1539098=20
width=3D96) (actual time=3D8396.404..8396.404 rows=3D1470174 loops=3D1)
Buckets: 131072 Batches: 2 Memory Usage: 97185kB
Buffers: shared hit=3D3 read=3D29488, temp written=
=3D11090
-> Seq Scan on vquest_aa (cost=3D0.00..44881.98=20
rows=3D1539098 width=3D96) (actual time=3D0.011..4288.422 rows=3D1470174 =
loops=3D1)
Buffers: shared hit=3D3 read=3D29488
-> Index Scan using biosequences_pkey on sequences s11=20
(cost=3D0.00..1173213.59 rows=3D23722060 width=3D103) (actual=20
time=3D36.189..118986.813 rows=3D23722060 loops=3D1)
Buffers: shared hit=3D257174 read=3D632027
-> Materialize (cost=3D13044857.94..13045857.94 rows=3D200000=20
width=3D1144) (actual time=3D1778162.821..1779107.784 rows=3D200000 loops=
=3D1)
Buffers: shared hit=3D2571669 read=3D6379728, temp written=3D214=
29
-> Sort (cost=3D13044857.94..13045357.94 rows=3D200000=20
width=3D1144) (actual time=3D1778160.740..1778467.544 rows=3D200000 loops=
=3D1)
Sort Key: aa.junction
Sort Method: quicksort Memory: 109142kB
Buffers: shared hit=3D2571669 read=3D6379728, temp written=
=3D21429
-> Merge Right Join (cost=3D11691423.56..12927442.30=20
rows=3D200000 width=3D1144) (actual time=3D1683740.302..1777062.899=20
rows=3D200000 loops=3D1)
Merge Cond: (s10.id =3D aa.jregion)
Buffers: shared hit=3D2571669 read=3D6379728, temp=20
written=3D21429
-> Index Scan using biosequences_pkey on sequences=20
s10 (cost=3D0.00..1173213.59 rows=3D23722060 width=3D103) (actual=20
time=3D0.012..157747.588 rows=3D23722058 loops=3D1)
Buffers: shared hit=3D257159 read=3D632042
-> Materialize (cost=3D11691423.56..11692423.56=20
rows=3D200000 width=3D1057) (actual time=3D1576243.597..1577200.558=20
rows=3D200000 loops=3D1)
Buffers: shared hit=3D2314510 read=3D5747686,=20
temp written=3D21429
-> Sort (cost=3D11691423.56..11691923.56=20
rows=3D200000 width=3D1057) (actual time=3D1576225.828..1576540.692=20
rows=3D200000 loops=3D1)
Sort Key: aa.jregion
Sort Method: quicksort Memory: 109141k=
B
Buffers: shared hit=3D2314510=20
read=3D5747686, temp written=3D21429
-> Merge Right Join=20
(cost=3D10344824.68..11580843.42 rows=3D200000 width=3D1057) (actual=20
time=3D1482245.997..1575195.906 rows=3D200000 loops=3D1)
Merge Cond: (s9.id =3D aa.cdr3)
Buffers: shared hit=3D2314510=20
read=3D5747686, temp written=3D21429
-> Index Scan using=20
biosequences_pkey on sequences s9 (cost=3D0.00..1173213.59 rows=3D237220=
60=20
width=3D103) (actual time=3D0.014..154690.793 rows=3D23722053 loops=3D1)
Buffers: shared hit=3D257156=
=20
read=3D632045
-> Materialize=20
(cost=3D10344824.68..10345824.68 rows=3D200000 width=3D970) (actual=20
time=3D1377571.940..1378572.983 rows=3D200000 loops=3D1)
Buffers: shared hit=3D205735=
4=20
read=3D5115641, temp written=3D21429
-> Sort=20
(cost=3D10344824.68..10345324.68 rows=3D200000 width=3D970) (actual=20
time=3D1377561.968..1377879.536 rows=3D200000 loops=3D1)
Sort Key: aa.cdr3
Sort Method:=20
quicksort Memory: 109137kB
Buffers: shared=20
hit=3D2057354 read=3D5115641, temp written=3D21429
-> Merge Right Join=20
(cost=3D9005743.80..10241762.54 rows=3D200000 width=3D970) (actual=20
time=3D1284751.972..1376566.143 rows=3D200000 loops=3D1)
Merge Cond:=20
(s8.id =3D aa.cdr2)
Buffers: shared=20
hit=3D2057354 read=3D5115641, temp written=3D21429
-> Index Scan=20
using biosequences_pkey on sequences s8 (cost=3D0.00..1173213.59=20
rows=3D23722060 width=3D103) (actual time=3D0.013..156466.277 rows=3D2372=
2052=20
loops=3D1)
Buffers:=20
shared hit=3D257169 read=3D632032
-> Materialize=20
(cost=3D9005743.80..9006743.80 rows=3D200000 width=3D883) (actual=20
time=3D1176878.668..1177863.431 rows=3D200000 loops=3D1)
Buffers:=20
shared hit=3D1800185 read=3D4483609, temp written=3D21429
-> Sort=20
(cost=3D9005743.80..9006243.80 rows=3D200000 width=3D883) (actual=20
time=3D1176876.609..1177193.551 rows=3D200000 loops=3D1)
=20
Sort Key: aa.cdr2
=20
Sort Method: quicksort Memory: 109106kB
=20
Buffers: shared hit=3D1800185 read=3D4483609, temp written=3D21429
->=20
Merge Right Join (cost=3D7674184.42..8910203.16 rows=3D200000 width=3D=
883)=20
(actual time=3D1086039.220..1176022.857 rows=3D200000 loops=3D1)
=20
Merge Cond: (s7.id =3D aa.cdr1)
=20
Buffers: shared hit=3D1800185 read=3D4483609, temp written=3D21429
=20
-> Index Scan using biosequences_pkey on sequences s7=20
(cost=3D0.00..1173213.59 rows=3D23722060 width=3D103) (actual=20
time=3D0.016..134532.516 rows=3D23722051 loops=3D1)
=20
Buffers: shared hit=3D257162 read=3D632039
=20
-> Materialize (cost=3D7674184.42..7675184.42 rows=3D200000 width=3D=
796)=20
(actual time=3D998770.854..999725.786 rows=3D200000 loops=3D1)
=20
Buffers: shared hit=3D1543023 read=3D3851570, temp written=3D21=
429
=20
-> Sort (cost=3D7674184.42..7674684.42 rows=3D200000 width=3D=
796)=20
(actual time=3D998768.908..999079.526 rows=3D200000 loops=3D1)
=20
Sort Key: aa.cdr1
=20
Sort Method: quicksort Memory: 109020kB
=20
Buffers: shared hit=3D1543023 read=3D3851570, temp writte=
n=3D21429
=20
-> Merge Right Join (cost=3D6350143.04..7586161.78=20
rows=3D200000 width=3D796) (actual time=3D924025.962..997633.963 rows=3D2=
00000=20
loops=3D1)
=20
Merge Cond: (s6.id =3D aa.fr4_imgt)
=20
Buffers: shared hit=3D1543023 read=3D3851570, temp=20
written=3D21429
=20
-> Index Scan using biosequences_pkey on=20
sequences s6 (cost=3D0.00..1173213.59 rows=3D23722060 width=3D103) (actu=
al=20
time=3D0.010..118666.804 rows=3D23722057 loops=3D1)
=20
Buffers: shared hit=3D257166 read=3D632035
=20
-> Materialize (cost=3D6350143.04..6351143.04=20
rows=3D200000 width=3D709) (actual time=3D836887.849..837838.133 rows=3D2=
00000=20
loops=3D1)
=20
Buffers: shared hit=3D1285857 read=3D3219535,=
=20
temp written=3D21429
=20
-> Sort (cost=3D6350143.04..6350643.04=20
rows=3D200000 width=3D709) (actual time=3D836885.917..837193.904 rows=3D2=
00000=20
loops=3D1)
=20
Sort Key: aa.fr4_imgt
=20
Sort Method: quicksort Memory: 109020=
kB
=20
Buffers: shared hit=3D1285857=20
read=3D3219535, temp written=3D21429
=20
-> Merge Right Join=20
(cost=3D5034123.16..6269641.90 rows=3D200000 width=3D709) (actual=20
time=3D762781.034..835798.442 rows=3D200000 loops=3D1)
=20
Merge Cond: (s5.id =3D aa.fr3_img=
t)
=20
Buffers: shared hit=3D1285857=20
read=3D3219535, temp written=3D21429
=20
-> Index Scan using=20
biosequences_pkey on sequences s5 (cost=3D0.00..1173213.59 rows=3D237220=
60=20
width=3D103) (actual time=3D0.010..118368.344 rows=3D23722056 loops=3D1)
=20
Buffers: shared hit=3D25715=
5=20
read=3D632046
=20
-> Sort=20
(cost=3D5034123.16..5034623.16 rows=3D200000 width=3D622) (actual=20
time=3D675955.766..676260.201 rows=3D200000 loops=3D1)
=20
Sort Key: aa.fr3_imgt
=20
Sort Method: quicksort=20
Memory: 108667kB
=20
Buffers: shared=20
hit=3D1028702 read=3D2587489, temp written=3D21429
=20
-> Merge Right Join=20
(cost=3D3780994.78..5016513.52 rows=3D200000 width=3D622) (actual=20
time=3D601634.880..674774.955 rows=3D200000 loops=3D1)
=20
Merge Cond: (s4.id =3D=
=20
aa.fr2_imgt)
=20
Buffers: shared=20
hit=3D1028702 read=3D2587489, temp written=3D21429
=20
-> Index Scan using=20
biosequences_pkey on sequences s4 (cost=3D0.00..1173213.59 rows=3D237220=
60=20
width=3D103) (actual time=3D0.010..118787.249 rows=3D23722055 loops=3D1)
=20
Buffers:=20
shared hit=3D257164 read=3D632037
=20
-> Sort=20
(cost=3D3780994.78..3781494.78 rows=3D200000 width=3D535) (actual=20
time=3D514392.602..514698.836 rows=3D200000 loops=3D1)
=20
Sort Key:=20
aa.fr2_imgt
=20
Sort Method:=20
quicksort Memory: 108667kB
=20
Buffers:=20
shared hit=3D771538 read=3D1955452, temp written=3D21429
=20
-> Merge=20
Right Join (cost=3D2527866.40..3763385.14 rows=3D200000 width=3D535) (ac=
tual=20
time=3D439805.541..512861.294 rows=3D200000 loops=3D1)
=20
Merge=20
Cond: (s3.id =3D aa.fr1_imgt)
=20
Buffers:=20
shared hit=3D771538 read=3D1955452, temp written=3D21429
=20
->=20
Index Scan using biosequences_pkey on sequences s3=20
(cost=3D0.00..1173213.59 rows=3D23722060 width=3D103) (actual=20
time=3D0.011..118178.639 rows=3D23722054 loops=3D1)
=20
=20
Buffers: shared hit=3D257180 read=3D632021
=20
-> Sort=20
(cost=3D2527866.40..2528366.40 rows=3D200000 width=3D448) (actual=20
time=3D353204.303..353511.857 rows=3D200000 loops=3D1)
=20
=20
Sort Key: aa.fr1_imgt
=20
=20
Sort Method: quicksort Memory: 108667kB
=20
=20
Buffers: shared hit=3D514358 read=3D1323431, temp written=3D21429
=20
->=20
Merge Right Join (cost=3D1274738.02..2510256.76 rows=3D200000 width=3D=
448)=20
(actual time=3D278817.368..351719.870 rows=3D200000 loops=3D1)
=20
=20
Merge Cond: (s2.id =3D aa.vregion)
=20
=20
Buffers: shared hit=3D514358 read=3D1323431, temp written=3D21429
=20
=20
-> Index Scan using biosequences_pkey on sequences s2=20
(cost=3D0.00..1173213.59 rows=3D23722060 width=3D103) (actual=20
time=3D0.010..117622.111 rows=3D23722060 loops=3D1)
=20
=20
Buffers: shared hit=3D257172 read=3D632029
=20
=20
-> Sort (cost=3D1274738.02..1275238.02 rows=3D200000 width=3D361)=20
(actual time=3D192528.719..192836.816 rows=3D200000 loops=3D1)
=20
=20
Sort Key: aa.vregion
=20
=20
Sort Method: quicksort Memory: 67612kB
=20
=20
Buffers: shared hit=3D257186 read=3D691402, temp written=3D214=
29
=20
=20
-> Merge Right Join (cost=3D21609.64..1257128.38 rows=3D2000=
00=20
width=3D361) (actual time=3D118193.401..191254.339 rows=3D200000 loops=3D=
1)
=20
=20
Merge Cond: (s1.id =3D aa.vdj)
=20
=20
Buffers: shared hit=3D257186 read=3D691402, temp written=
=3D21429
=20
=20
-> Index Scan using biosequences_pkey on sequences s1=20
(cost=3D0.00..1173213.59 rows=3D23722060 width=3D103) (actual=20
time=3D0.010..121574.630 rows=3D23722059 loops=3D1)
=20
=20
Buffers: shared hit=3D257180 read=3D632021
=20
=20
-> Sort (cost=3D21609.64..22109.64 rows=3D200000=20
width=3D274) (actual time=3D27974.028..28279.362 rows=3D200000 loops=3D1)
=20
=20
Sort Key: aa.vdj
=20
=20
Sort Method: quicksort Memory: 34270kB
=20
=20
Buffers: shared hit=3D6 read=3D59381, temp written=
=3D21429
=20
=20
-> CTE Scan on aa (cost=3D0.00..4000.00=20
rows=3D200000 width=3D274) (actual time=3D8464.001..27457.434 rows=3D2000=
00 loops=3D1)
=20
=20
Buffers: shared hit=3D6 read=3D59381, temp=20
written=3D21429
Total runtime: 1939690.783 ms
------------------------------------------



>
> was mir noch aufgefallen ist: "from vquest_aa limit XXX".
> das wird dir ein ziemlich zuf=E4lliges ergebnis geben ... das ergebnis =
ist ziemlich vom disk layout abh=E4ngig. will man das?

Im Endergebnis nicht. Da sollte die Query eigentlich mal alles zur=FCck=20
geben, diese "harte" Grenze gibt es aber auch, wenn man die=20
Ergebnismenge auf sinnvollere Art begrenzt, z.B. mit einem IN (...)=20
Statement.

>
> meine wette: der plan f=FCr die joins kippt um ...
>
> liebe grüÃe,
>
> hans


Vielen Dank und liebe GrüÃe,

Volker

>
>

--=20
Volker Sievert
AG Konthur
Max Planck Institute for Molecular Genetics
Department of Vertebrate Genomics (Prof. H. Lehrach)
Ihnestr.63, 14195 Berlin, Germany
Tel.: +49/30/ 8413-1578
Fax.: +49/30/ 8413-1718 or -1365
sievert@molgen.mpg.de
http://www.molgen.mpg.de/~in-vitro/

--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Report this message

#8: Re: [pgsql-de-allgemein] Re: [pgsql-de-allgemein] Performance bricht abrupt ein bei großen Ergebnism

Posted on 2011-09-30 15:46:23 by adsmail

Hallo,

Am 30.09.2011 15:28, schrieb Volker Sievert:
>
> ich muss zugeben, dass mich die Interpretation des Explain
> Analyse-Output etwas =FCberfordert (bin Biolge, kein Informatiker...) -
> versucht hatte ich das schon vorher mal und bin nicht so recht schlau
> daraus geworden.

Ich mache mal etwas Werbung: auf der bald stattfindenden=20
Deutschsprachigen PostgreSQL Konferenz wird es zu genau diesem Problem=20
auch Vortr=E4ge geben ;-)

Mehr Informationen unter:

http://2011.pgconf.de/


--=20
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project


--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Report this message

#9: Re: [pgsql-de-allgemein] Re: [pgsql-de-allgemein] Performance bricht abrupt ein bei großen Ergebnism

Posted on 2011-09-30 15:50:16 by Michael Renner

On Sep 30, 2011, at 15:28 , Volker Sievert wrote:

> ich muss zugeben, dass mich die Interpretation des Explain Analyse-Output=
etwas =FCberfordert (bin Biolge, kein Informatiker...) - versucht hatte ic=
h das schon vorher mal und bin nicht so recht schlau daraus geworden.

Bitte kipp die in http://explain.depesz.com/ ein, die sprengen die Dimensio=
nen die man inline im Mail noch sinnvoll verarbeiten kann ;)

lg,
michael
--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Report this message

#10: Re: [pgsql-de-allgemein] Re: [pgsql-de-allgemein] Performance bricht abrupt ein bei großen Ergebnism

Posted on 2011-09-30 16:21:25 by Volker Sievert

On 30.09.2011 15:50, Michael Renner wrote:
>
> On Sep 30, 2011, at 15:28 , Volker Sievert wrote:
>
>> ich muss zugeben, dass mich die Interpretation des Explain Analyse-Out=
put etwas =FCberfordert (bin Biolge, kein Informatiker...) - versucht hat=
te ich das schon vorher mal und bin nicht so recht schlau daraus geworden=
..
>
> Bitte kipp die in http://explain.depesz.com/ ein, die sprengen die Dime=
nsionen die man inline im Mail noch sinnvoll verarbeiten kann ;)
>
> lg,
> michael


OK,

damit siehts schon viel =FCbersichtlicher aus:

http://explain.depesz.com/s/HQu

Demzufolge braucht das Durchsuchen der Tabelle "sequences" die meiste=20
Zeit... nur was nun?

Danke & viele GrüÃe,

Volker


--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.o=
rg)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Report this message

#11: Re: [pgsql-de-allgemein] Re: [pgsql-de-allgemein] Re: [pgsql-de-allgemein] Performance bricht abrupt

Posted on 2011-09-30 16:29:54 by Michael Renner

On Sep 30, 2011, at 16:21 , Volker Sievert wrote:

> damit siehts schon viel =FCbersichtlicher aus:
>=20
> http://explain.depesz.com/s/HQu
>=20
> Demzufolge braucht das Durchsuchen der Tabelle "sequences" die meiste Zei=
t... nur was nun?


<wahrsagerkugel>

Die meiste Zeit wird in Index-Scans verbrannt, das sind relativ random-IO-l=
astige Operationen. Den Row-Counts zu Folge d=FCrfte der Planner da richtig=
entschieden haben; die Tables d=FCrften sehr sehr gro=DF sein.

Um das zu beschleunigen musst du entweder mehr Daten im shared buffer und O=
S page cache halten k=F6nnen (viel mehr RAM in die Kiste) oder schnellere B=
lockdevices besorgen (SSDs bieten sich an).

Oder die Daten f=FCr diese Art von abfragen generell anders aufbereiten und=
ablegen!

</wahrsagerkugel>

lg,
Michael
--=20
Sent via pgsql-de-allgemein mailing list (pgsql-de-allgemein@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-de-allgemein

Report this message

#12: Re: [pgsql-de-allgemein] [pgsql-de-allgemein] Performance bricht abrupt ein bei großen Ergebnismenge

Posted on 2011-09-30 21:43:50 by Hans-Juergen Schoenig

--Apple-Mail=_2004AFB3-FFD0-4282-A2CE-A132FD74FEE7
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=iso-8859-1

hallo ...

ich habe einfach mal ein kleines beispielhaftes ding rauskopiert:
du siehst einen index scan, der seine daten nur zu einem teil aus dem =
postgres shared buffer bezieht.je nach config kann das durchaus =
bedeuten, dass du eine menge random I/O hast und random I/O ist so =
ziemlich das teuerste, was es im database business gibt. auch das =
stecken von mehr disks wird in so einem fall nur bedingt helfen, weil du =
schlichtweg f=FCr einen ganzen haufen von bl=F6cken disk seeks =
aufgabelst.

Merge Cond: (s1.id =3D aa.vdj)
Buffers: shared hit=3D257186 read=3D691402, temp =
written=3D21429
-> Index Scan using biosequences_pkey on sequences s1 =
(cost=3D0.00..1173213.59 rows=3D23722060 width=3D103) (actual =
time=3D0.010..121574.630 rows=3D23722059 loops=3D1)
Buffers: shared hit=3D257180 read=3D632021

wenn du schaust: man sieht das auch bei "actual time" ... in diesen =
scans entsteht einfach die meiste zeit.

um einen vergleich zu haben, kannst du mal vor der query versuchen, die =
random I/O f=FCr den optimizer zu verteuern - vielleicht ben=F6tigst du =
ja genug daten, dass sich ein seq scan schon rechnet.
das geht so:

SET random_page_cost TO 20;
dann noch mal die query.=20
gut m=F6glich, dass bei ausreichend hohen random_page_costs statt dem =
index ein seq -> sort oder so raus kommt, der dann in summe schneller =
ist.
und; mehr ram + h=F6here shared buffers w=E4ren auch ein versuch wert.

lg,

hans



On Sep 30, 2011, at 3:28 PM, Volker Sievert wrote:

> Merge Cond: (s1.id =3D aa.vdj)
> Buffers: shared hit=3D257186 read=3D691402, temp =
written=3D21429
> -> Index Scan using biosequences_pkey on sequences s1 =
(cost=3D0.00..1173213.59 rows=3D23722060 width=3D103) (actual =
time=3D0.010..121574.630 rows=3D23722059 loops=3D1)
> Buffers: shared hit=3D257180 read=3D632021

--
Cybertec Sch=F6nig & Sch=F6nig GmbH
Gr=F6hrm=FChlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de


--Apple-Mail=_2004AFB3-FFD0-4282-A2CE-A132FD74FEE7
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
charset=iso-8859-1

<html><head></head><body style=3D"word-wrap: break-word; =
-webkit-nbsp-mode: space; -webkit-line-break: after-white-space; =
"><div>hallo ...</div><div><br></div><div>ich habe einfach mal ein =
kleines beispielhaftes ding rauskopiert:</div><div>du siehst einen index =
scan, der seine daten nur zu einem teil aus dem postgres shared buffer =
bezieht.je nach config kann das durchaus bedeuten, dass du eine menge =
random I/O hast und random I/O ist so ziemlich das teuerste, was es im =
database business gibt. auch das stecken von mehr disks wird in so einem =
fall nur bedingt helfen, weil du schlichtweg f=FCr einen ganzen haufen =
von bl=F6cken disk seeks aufgabelst.</div><div><br></div><div>&nbsp; =
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Merge Cond: (s1.id =3D =
aa.vdj)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;Buffers: shared hit=3D257186 read=3D691402, =
temp =
written=3D21429<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt; &nbsp;Index Scan using =
biosequences_pkey on sequences s1 &nbsp;(cost=3D0.00..1173213.59 =
rows=3D23722060 width=3D103) (actual time=3D0.010..121574.630 =
rows=3D23722059 =
loops=3D1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Buffers:=
shared hit=3D257180 read=3D632021</div><div><br></div><div>wenn du =
schaust: man sieht das auch bei "actual time" ... in diesen scans =
entsteht einfach die meiste zeit.</div><div><br></div><div>um einen =
vergleich zu haben, kannst du mal vor der query versuchen, die random =
I/O f=FCr den optimizer zu verteuern - vielleicht ben=F6tigst du ja =
genug daten, dass sich ein seq scan schon rechnet.</div><div>das geht =
so:</div><div><br></div><div>SET random_page_cost TO 20;</div><div>dann =
noch mal die query.&nbsp;</div><div>gut m=F6glich, dass bei ausreichend =
hohen random_page_costs statt dem index ein seq -&gt; sort oder so raus =
kommt, der dann in summe schneller ist.</div><div>und; mehr ram + h=F6here=
shared buffers w=E4ren auch ein versuch =
wert.</div><div><br></div><div><span class=3D"Apple-tab-span" =
style=3D"white-space:pre"> =
</span>lg,</div><div><br></div><div><span class=3D"Apple-tab-span" =
style=3D"white-space:pre"> =
</span>hans</div><div><br></div><div><br></div><br><div><div>On Sep 30, =
2011, at 3:28 PM, Volker Sievert wrote:</div><br =
class=3D"Apple-interchange-newline"><blockquote type=3D"cite"><span =
class=3D"Apple-style-span" style=3D"border-collapse: separate; =
font-family: Helvetica; font-style: normal; font-variant: normal; =
font-weight: normal; letter-spacing: normal; line-height: normal; =
orphans: 2; text-align: -webkit-auto; text-indent: 0px; text-transform: =
none; white-space: normal; widows: 2; word-spacing: 0px; =
-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: =
0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: =
auto; -webkit-text-stroke-width: 0px; font-size: medium; =
">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;Merge Cond: (s1.id =3D =
aa.vdj)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;Buffers: shared hit=3D257186 read=3D691402, =
temp =
written=3D21429<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;-&gt; &nbsp;Index Scan using =
biosequences_pkey on sequences s1 &nbsp;(cost=3D0.00..1173213.59 =
rows=3D23722060 width=3D103) (actual time=3D0.010..121574.630 =
rows=3D23722059 =
loops=3D1)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Buffers:=
shared hit=3D257180 read=3D632021</span></blockquote></div><br><div>
<span class=3D"Apple-style-span" style=3D"border-collapse: separate; =
color: rgb(0, 0, 0); font-family: Helvetica; font-style: normal; =
font-variant: normal; font-weight: normal; letter-spacing: normal; =
line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; =
text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; =
-webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: =
0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: =
auto; -webkit-text-stroke-width: 0px; font-size: medium; =
"><div><div>--</div><div>Cybertec Sch=F6nig &amp; Sch=F6nig =
GmbH</div><div>Gr=F6hrm=FChlgasse 26</div><div>A-2700 Wiener Neustadt, =
Austria</div><div>Web: <a =
href=3D"http://www.postgresql-support.de">http://www.postgre sql-support.de=
</a></div></div></span>
</div>
<br></body></html>=

--Apple-Mail=_2004AFB3-FFD0-4282-A2CE-A132FD74FEE7--

Report this message