Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 30C399FB332 for ; Thu, 5 Oct 2006 16:02:43 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 71847-06 for ; Thu, 5 Oct 2006 16:02:40 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.188]) by postgresql.org (Postfix) with ESMTP id 8ECAC9FB32A for ; Thu, 5 Oct 2006 16:02:39 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id p77so976992nfc for ; Thu, 05 Oct 2006 12:02:38 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:mime-version:content-type; b=A4JOEU8HMjrKympKgSDwVEl2pQLoPFO3e1ZUF4+/IYjS++PiJ5tWaqaQUdhvuV/wCsE7tCFq/270Mu3UiYCvolBsIoh47bYg+yqrT+s/katWpeSzgXenob46Jca7QaW6y0agPdmYOiXJzHGSlP1cqIBkwUM595uqNdVSBT/8Ql0= Received: by 10.49.94.20 with SMTP id w20mr4286715nfl; Thu, 05 Oct 2006 12:02:37 -0700 (PDT) Received: by 10.48.217.6 with HTTP; Thu, 5 Oct 2006 12:02:37 -0700 (PDT) Message-ID: Date: Thu, 5 Oct 2006 16:02:37 -0300 From: "=?ISO-8859-1?Q?William_Leite_Ara=FAjo?=" To: "=?ISO-8859-1?Q?Jos=E9_Carlos?=" Subject: Postgresql quey planner Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_341_21173352.1160074957407" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.696 tagged_above=0 required=5 tests=DNS_FROM_RFC_ABUSE, HTML_40_50, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200610/66 X-Sequence-Number: 26455 ------=_Part_341_21173352.1160074957407 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Estou confuso com o funcionamento do "query planner" do postgresql. Tenho 2 queries conceitualmente id=EAnticas: I'm confused about the planner functionality. I'd 2 queries contextualy indentical: SELECT p.pos_id, count(aut_id) as pesados FROM posto p LEFT OUTER JOIN pesagem e USING(pos_id) WHERE e.pos_id IS NULL OR (pes_dat_tstam BETWEEN '2006-03-01' AND '2006-03-31') GROUP BY p.pos_id ORDER BY pos_id SELECT p.pos_id, count(pes_id) as autuados FROM posto p LEFT OUTER JOIN autuacao a USING(pos_id) WHERE a.pos_id IS NULL OR (aut_dat_tstam BETWEEN '2006-03-01' AND '2006-03-31') GROUP BY p.pos_id ORDER BY pos_id A segunda faz realmente o que eu desejo. Mostra todos os postos, inclusive os que n=E3o apresentam resultado. J=E1 a primeira, alguns postos simplesmente n=E3o aparecem. A diferen=E7a entre as tabelas =E9 o n=FAmero = de registros. A tabela de "pesagem" possui muito mais registros que a de "autuacao". Avaliando o planner, v=ED que realmente trada de forma muito diferente as consultas, mas n=E3o entendi porque o resultado e afetado. N= =E3o deveria. A saida do planner =E9 : The second does really what I desire. Show all records in table "posto", including that's without count in table "autuados". But the first, doesn't. Some elements on table "Posto" are ommited. Seeing the planner output, I couldn't understand why he uses "GroupAggregate" on the first but not on the second query. The relevant difference on tables is the number of records. "pesagem" has about 22000 but "autuacao" only 100. Is my queries wrongs? 'GroupAggregate (cost=3D0.00..1027.66 rows=3D10 width=3D8) (actual time=3D 0.318..30.741 rows=3D6 loops=3D1)' ' -> Merge Left Join (cost=3D0.00..1027.43 rows=3D21 width=3D8) (actual = time=3D 0.289..30.651 rows=3D54 loops=3D1)' ' Merge Cond: ("outer".pos_id =3D "inner".pos_id)' ' Filter: (("inner".pos_id IS NULL) OR (("inner".pes_dat_tstam >=3D '2006-03-01'::date) AND ("inner".pes_dat_tstam <=3D '2006-03-31'::date)))' ' -> Index Scan using prk_posto on posto p (cost=3D0.00..5.35 rows= =3D10 width=3D4) (actual time=3D0.064..0.081 rows=3D10 loops=3D1)' ' -> Index Scan using fki_frk_pos_id on pesagem a (cost=3D 0.00..801.48 rows=3D12604 width=3D12) (actual time=3D0.059..16.331 rows=3D1= 2604 loops=3D1)' 'Total runtime: 31.035 ms' 'Sort (cost=3D8.38..8.41 rows=3D10 width=3D8) (actual time=3D0.557..0.564 = rows=3D10 loops=3D1)' ' Sort Key: p.pos_id' ' -> HashAggregate (cost=3D8.09..8.22 rows=3D10 width=3D8) (actual time= =3D 0.520..0.532 rows=3D10 loops=3D1)' ' -> Merge Right Join (cost=3D1.27..7.85 rows=3D49 width=3D8) (act= ual time=3D0.062..0.443 rows=3D58 loops=3D1)' ' Merge Cond: ("outer".pos_id =3D "inner".pos_id)' ' Filter: (("outer".pos_id IS NULL) OR (("outer".aut_dat_tstam >=3D '2006-03-01'::date) AND ("outer".aut_dat_tstam <=3D '2006-03-31'::date= )))' ' -> Index Scan using idx_autuacao_pos_id on autuacao a (cost=3D0.00..4.85 rows=3D77 width=3D12) (actual time=3D0.007..0.103 rows= =3D77 loops=3D1)' ' -> Sort (cost=3D1.27..1.29 rows=3D10 width=3D4) (actual ti= me=3D 0.049..0.107 rows=3D86 loops=3D1)' ' Sort Key: p.pos_id' ' -> Seq Scan on posto p (cost=3D0.00..1.10 rows=3D10 width=3D4) (actual time=3D0.005..0.016 rows=3D10 loops=3D1)' 'Total runtime: 0.659 ms' N=E3o consigo entender porqu=EA o primeiro usa no final "GroupAggrega= te" mas o segundo n=E3o. =C9 erro? Atenciosamente, --=20 William Leite Ara=FAjo ------=_Part_341_21173352.1160074957407 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline       Estou confuso com o funcionamento do "q= uery planner" do postgresql. Tenho 2 queries conceitualmente id=EAntic= as:
      I'm confused about the planner functi= onality. I'd 2 queries contextualy indentical:

SELECT p.pos_id, count(aut_id) as pesados
FROM posto p LEFT OUTER JOIN=20 pesagem e USING(pos_id)WHERE e.pos_id IS NULL OR (pes_dat_tstam BETWEEN '2006-03-01' <= span style=3D"font-weight: bold;">AND '2006-03-31')
GROUP B= Y p.pos_id ORDER BY pos_id

=
SELECT p.pos_id, count(pes_id) as autuados
FROM posto p LEFT OUTER JOIN autuacao a USING(pos_id)
WHERE a.pos_id IS NULL OR (aut_dat_tstam BETWEEN= '2006-03-01' AND GROUP BY p.pos_id ORDER BY pos_id

     A segunda faz realmente o que eu desejo. Most= ra todos os postos, inclusive os que n=E3o apresentam resultado. J=E1 a pri= meira, alguns postos simplesmente n=E3o aparecem. A diferen=E7a entre as ta= belas =E9 o n=FAmero de registros. A tabela de "pesagem" possui m= uito mais registros que a de "autuacao". Avaliando o planner, v= =ED que realmente trada de forma muito diferente as consultas, mas n=E3o en= tendi porque o resultado e afetado. N=E3o deveria. A saida do planner =E9 :

     The second does really what I desire. Show= all records in table "posto", including that's without count in = table "autuados". But the first, doesn't. Some elements on table = "Posto" are ommited. Seeing the planner output, I couldn't unders= tand why he uses "GroupAggregate" on the first but not on the sec= ond query. The relevant difference on tables is the number of records. &quo= t;pesagem" has about 22000 but "autuacao" only 100. Is my qu= eries wrongs?=20

'GroupAggregate  (cost=3D0.00..1027.66 rows=3D10 width=3D8) (a= ctual time=3D0.318..30.741 rows=3D6 loops=3D1)'
'  ->  Merg= e Left Join  (cost=3D0.00..1027.43 rows=3D21 width=3D8) (actual time= =3D0.289..30.651 rows=3D54 loops=3D1)'
'        Merge Cond: ("outer".= pos_id =3D "inner".pos_id)'
'     &nb= sp;  Filter: (("inner".pos_id IS NULL) OR (("inner"= ;.pes_dat_tstam >=3D '2006-03-01'::date) AND ("inner".pes_dat_= tstam <=3D '2006-03-31'::date)))'
'        ->  Index Scan usin= g prk_posto on posto p  (cost=3D0.00..5.35 rows=3D10 width=3D4) (actua= l time=3D0.064..0.081 rows=3D10 loops=3D1)'
'    &nb= sp;   ->  Index Scan using fki_frk_pos_id on pesagem a&nb= sp; (cost=3D0.00..801.48 rows=3D12604 width=3D12) (actual time=3D 0.059..16.331 rows=3D12604 loops=3D1)'
'Total runtime: 31.035 ms'

'Sort  (cost=3D8.38..8.41 rows=3D10 width=3D8) (actual time=3D0.5= 57..0.564 rows=3D10 loops=3D1)'
'  Sort Key: p.pos_id'
'  -= >  HashAggregate  (cost=3D 8.09..8.22 rows=3D10 width=3D8) (actual time=3D0.520..0.532 rows=3D10 loops= =3D1)'
'        ->  Merge Rig= ht Join  (cost=3D1.27..7.85 rows=3D49 width=3D8) (actual time=3D0.062.= .0.443 rows=3D58 loops=3D1)'
'       =        Merge Cond: ("outer".pos_id = =3D "inner".pos_id)'
'           &nbs= p;  Filter: (("outer".pos_id IS NULL) OR (("outer"= .aut_dat_tstam >=3D '2006-03-01'::date) AND ("outer".aut_dat_t= stam <=3D '2006-03-31'::date)))'
'      = ;        ->  Index Scan using id= x_autuacao_pos_id on autuacao a  (cost=3D 0.00..4.85 rows=3D77 width=3D12) (actual time=3D0.007..0.103 rows=3D77 loop= s=3D1)'
'          &nb= sp;   ->  Sort  (cost=3D1.27..1.29 rows=3D10 width= =3D4) (actual time=3D0.049..0.107 rows=3D86 loops=3D1)'
'  &nb= sp;            =      Sort Key: p.pos_id '
'           &nb= sp;        ->  Seq Scan on posto= p  (cost=3D0.00..1.10 rows=3D10 width=3D4) (actual time=3D0.005..0.01= 6 rows=3D10 loops=3D1)'
'Total runtime: 0.659 ms'

  &nb= sp;   N=E3o consigo entender porqu=EA o primeiro usa no final &qu= ot;GroupAggregate" mas o segundo n=E3o. =C9 erro?
      Atenciosamente,
--
William Leite = Ara=FAjo
------=_Part_341_21173352.1160074957407--