X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F1F49D85B7 for ; Thu, 13 Oct 2005 06:54:01 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 38502-09 for ; Thu, 13 Oct 2005 09:53:56 +0000 (GMT) Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.177]) by svr1.postgresql.org (Postfix) with ESMTP id 87EB9D854B for ; Thu, 13 Oct 2005 06:53:57 -0300 (ADT) Received: from p508E65E8.dip.t-dialin.net [80.142.101.232] (helo=swtexchange2.technology.de) by mrelayeu.kundenserver.de with ESMTP (Nemesis), id 0MKwh2-1EPzmg3Ttz-0007On; Thu, 13 Oct 2005 11:53:58 +0200 content-class: urn:content-classes:message Subject: Optimizer misconfigured ? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5CFDC.07A4960E" Date: Thu, 13 Oct 2005 11:53:57 +0200 X-MimeOLE: Produced By Microsoft Exchange V6.0.6556.0 Message-ID: <16F953410A0F1346848DCB476A989CFE01D52D@swtexchange2.technology.de> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Optimizer misconfigured ? Thread-Index: AcXP3AfeUE8Qtq0TTjCmTU9RRV9p2Q== From: =?iso-8859-1?Q?N=F6rder-Tuitje=2C_Marcus?= To: X-Provags-ID: kundenserver.de abuse@kundenserver.de login:24478085a861b01e05bdc3b8f80dc176 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.353 required=5 tests=[FORGED_RCVD_HELO=0.05, HTML_50_60=0.095, HTML_MESSAGE=0.001, UPPERCASE_25_50=0.207] X-Spam-Level: X-Archive-Number: 200510/219 X-Sequence-Number: 14987 This is a multi-part message in MIME format. ------_=_NextPart_001_01C5CFDC.07A4960E Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello,=20 I have a strange effect on upcoming structure : DEX_OBJ ---< DEX_STRUCT >--- DEX_LIT DEX_OBJ : 100 records (#DOO_ID, DOO_NAME) DEX_STRUCT : 2,5 million records (#(DST_SEQ, FK_DOO_ID, FK_LIT_ID)) DEX_LIT : 150K records (#LIT_ID, LIT_TEXT) (# marks primary key) i'd like to count all LIT occurences in struct for a set of LITs. so i indexed DEX_STRUCT using (FK_LIT_ID, FK_DOO_ID) and i indexed DEX_LIT using BTREE (LIT_TEXT, LIT_ID) but if i query SELECT DOO_ID , COUNT(FK_LIT_ID) AS occurences FROM DEX_STRUCT STR , DEX_LITERAL LIT WHERE STR.FK_LIT_ID =3D LIT.LIT_ID AND LIT_TEXT IN ('foo', 'bar', 'foobar') GROUP BY DOO_ID postgresql always runs a seq scan on DEX_STRUCT. I tried several indices = and also very different kinds of queries (from EXISTS via INNER JOIN up = to subqueries), but Pgsql does not use any index on dex_struct. What can I do ? Is this a optimizer misconfiguration (hence, it is still = in default config) ? How can I make Pg using the indices on doc_struct ? The index on LIT is = used :-( I expect 30 - 60 millions of records in the struct table, so I urgently = need indexed access. Thanks a lot ! Marcus ------_=_NextPart_001_01C5CFDC.07A4960E Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Optimizer misconfigured ?

Hello,

I have a strange effect on upcoming = structure :


DEX_OBJ  ---< DEX_STRUCT = >--- DEX_LIT

DEX_OBJ : 100 records (#DOO_ID, = DOO_NAME)
DEX_STRUCT : 2,5 million records  = (#(DST_SEQ, FK_DOO_ID, FK_LIT_ID))
DEX_LIT : 150K records  (#LIT_ID, = LIT_TEXT)

(# marks primary key)

i'd like to count all LIT occurences in = struct for a set of LITs.

so i indexed DEX_STRUCT using = (FK_LIT_ID, FK_DOO_ID)
and i indexed DEX_LIT using BTREE = (LIT_TEXT, LIT_ID)

but if i query

SELECT DOO_ID
    ,    = COUNT(FK_LIT_ID) AS occurences
   FROM DEX_STRUCT  = STR
      ,  = DEX_LITERAL  LIT
WHERE STR.FK_LIT_ID =3D = LIT.LIT_ID
     AND  = LIT_TEXT IN ('foo', 'bar', 'foobar')
  GROUP BY DOO_ID

postgresql always runs a seq scan on = DEX_STRUCT. I tried several indices and also very different kinds of = queries (from EXISTS via INNER JOIN up to subqueries), but Pgsql does = not use any index on dex_struct.

What can I do ? Is this a optimizer = misconfiguration (hence, it is still in default config) ?

How can I make Pg using the indices on = doc_struct ? The index on LIT is used :-(

I expect 30 - 60 millions of records in = the struct table, so I urgently need indexed access.

Thanks a lot !

Marcus

------_=_NextPart_001_01C5CFDC.07A4960E--