X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 4D16B9DD67A for ; Thu, 1 Dec 2005 11:49:25 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 44936-05 for ; Thu, 1 Dec 2005 11:49:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25]) by postgresql.org (Postfix) with ESMTP id 4A1C79DD592 for ; Thu, 1 Dec 2005 11:49:19 -0400 (AST) Received: from smtpgw.computec.de (unknown [212.123.108.8]) by svr2.postgresql.org (Postfix) with ESMTP id 84953F0C19 for ; Thu, 1 Dec 2005 15:49:22 +0000 (GMT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtpgw.computec.de (Postfix) with ESMTP id EA115559C4 for ; Thu, 1 Dec 2005 16:49:16 +0100 (CET) Received: from smtpgw.computec.de ([127.0.0.1]) by localhost (localhost [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 15659-01-48 for ; Thu, 1 Dec 2005 16:49:15 +0100 (CET) Received: from HERMES.computec.de (unknown [192.168.0.22]) by smtpgw.computec.de (Postfix) with ESMTP id C1701559C9 for ; Thu, 1 Dec 2005 16:49:15 +0100 (CET) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Queries taking ages in PG 8.1, have been much faster in PG<=8.0 Date: Thu, 1 Dec 2005 16:49:14 +0100 Message-ID: <28011CD60FB1724DBA4442E38277F6264A626A@hermes.computec.de> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Queries taking ages in PG 8.1, have been much faster in PG<=8.0 Thread-Index: AcX2jsfXE/UysVCLRoefDJ7OfTLamw== From: "Markus Wollny" To: X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at computec.de X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.919 required=5 tests=[DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_POST=1.44] X-Spam-Score: 1.919 X-Spam-Level: * X-Archive-Number: 200512/16 X-Sequence-Number: 15837 Hi! I've got an urgent problem with an application which is evaluating a monthly survey; it's running quite a lot of queries like this: select SOURCE.NAME as TYPE, count(PARTICIPANT.SESSION_ID) as TOTAL from ( select PARTICIPANT.SESSION_ID from survey.PARTICIPANT, survey.ANSWER where PARTICIPANT.STATUS =3D 1 and date_trunc('month', PARTICIPANT.CREATED) =3D = date_trunc('month', now()-'1 month'::interval) and PARTICIPANT.SESSION_ID =3D ANSWER.SESSION_ID and ANSWER.QUESTION_ID =3D 6 and ANSWER.VALUE =3D 1 ) as PARTICIPANT, survey.ANSWER, survey.HANDY_JAVA SOURCE where PARTICIPANT.SESSION_ID =3D ANSWER.SESSION_ID and ANSWER.QUESTION_ID =3D 16 and ANSWER.VALUE =3D SOURCE.ID group by SOURCE.NAME, SOURCE.POSITION order by SOURCE.POSITION asc; My current PostgreSQL-version is "PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2". Up to 8.0, a query like this took a couple of seconds, maybe even up to a minute. In 8.1 a query like this will run from 30 minutes up to two hours to complete, depending on ist complexity. I've got autovaccum enabled and run a nightly vacuum analyze over all of my databases. Here's some information about the relevant tables: Table answer has got ~ 8.9M rows (estimated 8,872,130, counted 8,876,648), participant has got ~178K rows (estimated 178,165, counted 178,248), HANDY_JAVA has got three rows. This is the explain-analyze-output for the above: "Sort (cost=3D11383.09..11383.10 rows=3D3 width=3D16) (actual time=3D1952676.858..1952676.863 rows=3D3 loops=3D1)" " Sort Key: source."position"" " -> HashAggregate (cost=3D11383.03..11383.07 rows=3D3 width=3D16) = (actual time=3D1952676.626..1952676.635 rows=3D3 loops=3D1)" " -> Nested Loop (cost=3D189.32..11383.00 rows=3D5 width=3D16) (actual time=3D6975.812..1952371.782 rows=3D9806 loops=3D1)" " -> Nested Loop (cost=3D3.48..3517.47 rows=3D42 = width=3D20) (actual time=3D6819.716..15419.930 rows=3D9806 loops=3D1)" " -> Nested Loop (cost=3D3.48..1042.38 rows=3D738 width=3D16) (actual time=3D258.434..6233.039 rows=3D162723 loops=3D1)" " -> Seq Scan on handy_java source (cost=3D0.00..1.03 rows=3D3 width=3D14) (actual time=3D0.093..0.118 = rows=3D3 loops=3D1)" " -> Bitmap Heap Scan on answer (cost=3D3.48..344.04 rows=3D246 width=3D8) (actual = time=3D172.381..1820.499 rows=3D54241 loops=3D3)" " Recheck Cond: ((answer.question_id =3D 16) AND (answer.value =3D "outer".id))" " -> Bitmap Index Scan on idx02_performance (cost=3D0.00..3.48 rows=3D246 width=3D0) (actual time=3D98.321..98.321 rows=3D54245 loops=3D3)" " Index Cond: ((answer.question_id =3D 16) AND (answer.value =3D "outer".id))" " -> Index Scan using idx01_perf_0006 on participant (cost=3D0.00..3.34 rows=3D1 width=3D4) (actual time=3D0.049..0.050 = rows=3D0 loops=3D162723)" " Index Cond: (participant.session_id =3D "outer".session_id)" " Filter: ((status =3D 1) AND (date_trunc('month'::text, created) =3D date_trunc('month'::text, (now() = - '1 mon'::interval))))" " -> Bitmap Heap Scan on answer (cost=3D185.85..187.26 rows=3D1 width=3D4) (actual time=3D197.490..197.494 rows=3D1 = loops=3D9806)" " Recheck Cond: (("outer".session_id =3D answer.session_id) AND (answer.question_id =3D 6) AND (answer.value =3D = 1))" " -> BitmapAnd (cost=3D185.85..185.85 rows=3D1 = width=3D0) (actual time=3D197.421..197.421 rows=3D0 loops=3D9806)" " -> Bitmap Index Scan on idx_answer_session_id (cost=3D0.00..2.83 rows=3D236 width=3D0) (actual time=3D0.109..0.109 rows=3D49 loops=3D9806)" " Index Cond: ("outer".session_id =3D answer.session_id)" " -> Bitmap Index Scan on idx02_performance (cost=3D0.00..182.77 rows=3D20629 width=3D0) (actual = time=3D195.742..195.742 rows=3D165697 loops=3D9806)" " Index Cond: ((question_id =3D 6) AND (value =3D 1))" "Total runtime: 1952678.393 ms" I am really sorry, but currently I haven't got any 8.0-installation left, so I cannot provide the explain (analyze) output for 8.0.=20 I fiddled a little with the statement and managed to speed things up quite a lot: =20 select SOURCE.NAME as TYPE, count(ANSWER.SESSION_ID) as TOTAL from survey.ANSWER, survey.HANDY_JAVA SOURCE where ANSWER.QUESTION_ID =3D 16 and ANSWER.VALUE =3D SOURCE.ID and ANSWER.SESSION_ID in ( select PARTICIPANT.SESSION_ID from survey.PARTICIPANT, survey.ANSWER where PARTICIPANT.STATUS =3D 1 and date_trunc('month', PARTICIPANT.CREATED) =3D = date_trunc('month', now()-'1 month'::interval) and PARTICIPANT.SESSION_ID =3D ANSWER.SESSION_ID and ANSWER.QUESTION_ID =3D 6 and ANSWER.VALUE =3D 1 ) group by SOURCE.NAME, SOURCE.POSITION order by SOURCE.POSITION asc; Here's the explain analyze output: "Sort (cost=3D27835.39..27835.39 rows=3D3 width=3D16) (actual time=3D9609.207..9609.212 rows=3D3 loops=3D1)" " Sort Key: source."position"" " -> HashAggregate (cost=3D27835.33..27835.36 rows=3D3 width=3D16) = (actual time=3D9609.058..9609.067 rows=3D3 loops=3D1)" " -> Hash IN Join (cost=3D26645.78..27835.29 rows=3D5 = width=3D16) (actual time=3D6374.436..9548.945 rows=3D9806 loops=3D1)" " Hash Cond: ("outer".session_id =3D "inner".session_id)" " -> Nested Loop (cost=3D3.48..1042.38 rows=3D738 = width=3D16) (actual time=3D190.419..4817.977 rows=3D162704 loops=3D1)" " -> Seq Scan on handy_java source = (cost=3D0.00..1.03 rows=3D3 width=3D14) (actual time=3D0.036..0.058 rows=3D3 loops=3D1)" " -> Bitmap Heap Scan on answer = (cost=3D3.48..344.04 rows=3D246 width=3D8) (actual time=3D116.719..1390.931 rows=3D54235 = loops=3D3)" " Recheck Cond: ((answer.question_id =3D 16) = AND (answer.value =3D "outer".id))" " -> Bitmap Index Scan on idx02_performance (cost=3D0.00..3.48 rows=3D246 width=3D0) (actual time=3D63.195..63.195 rows=3D54235 loops=3D3)" " Index Cond: ((answer.question_id =3D = 16) AND (answer.value =3D "outer".id))" " -> Hash (cost=3D26639.37..26639.37 rows=3D1174 = width=3D8) (actual time=3D3906.831..3906.831 rows=3D9806 loops=3D1)" " -> Hash Join (cost=3D4829.24..26639.37 = rows=3D1174 width=3D8) (actual time=3D464.011..3877.539 rows=3D9806 loops=3D1)" " Hash Cond: ("outer".session_id =3D "inner".session_id)" " -> Bitmap Heap Scan on answer (cost=3D182.76..21413.93 rows=3D20626 width=3D4) (actual time=3D273.839..2860.984 rows=3D165655 loops=3D1)" " Recheck Cond: ((question_id =3D 6) AND (value =3D 1))" " -> Bitmap Index Scan on idx02_performance (cost=3D0.00..182.76 rows=3D20626 width=3D0) (actual time=3D171.933..171.933 rows=3D165659 loops=3D1)" " Index Cond: ((question_id =3D 6) AND (value =3D 1))" " -> Hash (cost=3D4621.13..4621.13 = rows=3D10141 width=3D4) (actual time=3D123.351..123.351 rows=3D11134 loops=3D1)" " -> Index Scan using idx01_perf_0005 on participant (cost=3D0.01..4621.13 rows=3D10141 width=3D4) (actual time=3D0.545..93.200 rows=3D11134 loops=3D1)" " Index Cond: (date_trunc('month'::text, created) =3D date_trunc('month'::text, (now() = - '1 mon'::interval)))" " Filter: (status =3D 1)" "Total runtime: 9612.249 ms" Regarding the total runtime, this is roughly in the same dimension as for the original query in 8.0, as far as I remember. I haven't written these queries myself in the first place, but they were done in the dark ages when IN was a no-no and haven't been touched ever since - there hadn't been any need to do so. My current problem is that rewriting hundreds of queries, some of them quite a bit more complex than this one, but all of them using the same general scheme, would take quite a lot of time - and I'm expected to hand over the survey results ASAP. So I will obviously have to do a rewrite if there's just no other way, but I wondered if there might be some other option that would allow me to point the planner in the right direction so it would behave the same as in the previous versions, namely 8.0? Any suggestions? Kind regards Markus