This is a multi-part message in MIME format.
------_=_NextPart_001_01C8809C.43A6353A
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
=0D=0AB,=0D=0A =0D=0ADid you put in a compound index or do you have 2
ind=
exes? You may want=0D=0Ato try a compound index and also if this is a
comm=
on join, you may want=0D=0Ato cluster both tables on that compound
index=2E=
=0D=0A =0D=0ACompound index looks something like this=0D=0A=0D=0ACREATE
IND=
EX idx_bigtable_destorig =0D=0AON bigtable USING btree=0D=0A (originid,
=
destinationid);=0D=0A =0D=0AAlso if the compound represents a unique
record=
, you will want to make=0D=0Asure you create either a unique or primary
key=
index on that=2E The=0D=0Aplanner uses that information=2E For example
if=
it is unique in your small=0D=0Atable - you would setup the index
like=0D=
=0ACREATE UNIQUE INDEX idx_smalltable_destorig =0D=0AON smalltable USING
=
btree (originid, destinationid); =0D=0A =0D=0A=0D=0AWhich version of
Postgr=
eSQL are you using by the way? So many changes=0D=0Ain the planner have
be=
en made between 8 and 8=2E3 that it would be helpful=0D=0Ato know this=2E=
=0D=0A =0D=0ADid you vacuum analyze before hand to make sure the planner
ha=
s up to=0D=0Adate stats?=0D=0A =0D=0AHope that helps,=0D=0ARegina=0D=0A
=0D=
=0A From:
pgsql-novice-owner@[EMAIL PROTECTED]
On Behalf Of Chigoy, Byron T=0D=0ASent: Friday, March
=
07, 2008 3:19 PM=0D=0ATo: pgsql-novice@[EMAIL PROTECTED]
Optimizing Query (Index)=0D=0A=0D=0A=0D=0A=0D=0AHi,=0D=0A=0D=0A
=0D=0A=
=0D=0AI have two tables; table1 has 3500 records (small table) and table2=
=0D=0A(bigtable) has 25 million records=2E=0D=0A=0D=0A =0D=0A=0D=0AI need
t=
o Inner Join the two tables based on two columns in each
table=2E=0D=0A=0D=
=0A =0D=0A=0D=0AMy query reads (I have changed the literal names here for
e=
asier=0D=0Areading):=0D=0A=0D=0A =0D=0A=0D=0ASELECT sm=2Erecordid,
sm=2Eare=
aid, bg=2Edistance=0D=0A=0D=0AFROM smalltable sm =0D=0A=0D=0AINNER JOIN
big=
table bg ON (bg=2Eoriginid=3Dsm=2Eoriginid
AND=0D=0Abg=2Edetinationid=3Dsm=
=2Edetinationid)=0D=0A=0D=0AWHERE sm=2Esomecondition>0=0D=0A=0D=0A =0D=0A=
=0D=0AI have tried indexing the originid and destinationid columns in
both=
=0D=0Atables, and indexed the somecondition column but my query is
still=0D=
=0Arunning extraordinarily slow=2E I looked at the query plan, and it
seem=
s=0D=0Athat my query plan only references the index on the
somecondition=0D=
=0Acolumn=2E I need to make this run much faster, any tips?=0D=0A=0D=0A =
=0D=0A=0D=0ARegards,=0D=0A=0D=0AB=0D=0A=0D=0A=0D=0A=0D=0A------------------=
-----------------------=0D=0AThe substance of this message, including any
a=
ttachments, may be=0Aconfidential, legally privileged and/or exempt from
di=
sclosure=0Apursuant to Massachusetts law=2E It is intended=0D=0Asolely for
=
the addressee=2E If you received this in error, please=0Acontact the
sender=
and delete the material from any computer=2E=0D=0A
------_=_NextPart_001_01C8809C.43A6353A
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4=2E0
Transitional//EN">=0D=0A<HTML=
xmlns=3D"http://www=2Ew3=2Eorg/TR/REC-html40"
xmlns:v =3D
=0D=0A"urn:schem=
as-microsoft-com:vml" xmlns:o =3D
=0D=0A"urn:schemas-microsoft-com:office:o=
ffice" xmlns:w =3D
=0D=0A"urn:schemas-microsoft-com:office:word"><HEAD>=0D=
=0A<META http-equiv=3DContent-Type content=3D"text/html;
charset=3Dus-ascii=
">=0D=0A<META content=3D"MSHTML 6=2E00=2E6000=2E16481"
name=3DGENERATOR>=0D=
=0A<STYLE>@[EMAIL PROTECTED]
Section1 {size: 8=2E5in 11=2E0in; margin: 1=2E0in 1=2E25in
=
1=2E0in 1=2E25in; }=0D=0AP=2EMsoNormal {=0D=0A FONT-SIZE: 12pt; MARGIN:
0in=
0in 0pt; FONT-FAMILY: "Times New Roman"=0D=0A}=0D=0ALI=2EMsoNormal
{=0D=0A=
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New
Roman"=0D=0A=
}=0D=0ADIV=2EMsoNormal {=0D=0A FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt;
FONT-F=
AMILY: "Times New Roman"=0D=0A}=0D=0AA:link {=0D=0A COLOR: blue;
TEXT-DECOR=
ATION: underline=0D=0A}=0D=0ASPAN=2EMsoHyperlink {=0D=0A COLOR: blue;
TEXT-=
DECORATION: underline=0D=0A}=0D=0AA:visited {=0D=0A COLOR: purple;
TEXT-DEC=
ORATION: underline=0D=0A}=0D=0ASPAN=2EMsoHyperlinkFollowed {=0D=0A COLOR:
p=
urple; TEXT-DECORATION: underline=0D=0A}=0D=0ASPAN=2EEmailStyle17
{=0D=0A C=
OLOR: windowtext; FONT-FAMILY: Arial; mso-style-type: personal-compose=0D=
=0A}=0D=0ADIV=2ESection1 {=0D=0A page:
Section1=0D=0A}=0D=0A</STYLE>=0D=0A<=
!--[if gte mso 9]><xml>=0D=0A <o:shapedefaults v:ext=3D"edit"
spidmax=3D"10=
26" />=0D=0A</xml><![endif]--><!--[if gte mso 9]><xml>=0D=0A
<o:shapelayout=
v:ext=3D"edit">=0D=0A <o:idmap v:ext=3D"edit" data=3D"1" />=0D=0A
</o:sha=
pelayout></xml><![endif]--></HEAD>=0D=0A<BODY lang=3DEN-US vLink=3Dpurple
l=
ink=3Dblue>=0D=0A<DIV dir=3Dltr align=3Dleft><FONT face=3DTahoma><FONT
size=
=3D2><SPAN =0D=0Aclass=3D146023221-07032008><FONT face=3DArial
color=3D#000=
0ff> </FONT>=0D=0A<DIV dir=3Dltr align=3Dleft><SPAN
class=3D146023221-=
07032008><FONT face=3DArial =0D=0Acolor=3D#0000ff
size=3D2>B,</FONT></SPAN>=
</DIV>=0D=0A<DIV dir=3Dltr align=3Dleft><SPAN
class=3D146023221-07032008><F=
ONT face=3DArial =0D=0Acolor=3D#0000ff
size=3D2></FONT></SPAN> </DIV>=
=0D=0A<DIV dir=3Dltr align=3Dleft><SPAN class=3D146023221-07032008><FONT
fa=
ce=3DArial =0D=0Acolor=3D#0000ff size=3D2>Did you put in a compound index
o=
r do you have 2 =0D=0Aindexes? You may want to try a compound
in=
dex and also if this is a =0D=0Acommon join, you may want to cluster both
t=
ables on that compound =0D=0Aindex=2E</FONT></SPAN></DIV>=0D=0A<DIV
dir=3Dl=
tr align=3Dleft><SPAN class=3D146023221-07032008><FONT face=3DArial
=0D=0Ac=
olor=3D#0000ff size=3D2></FONT></SPAN> </DIV>=0D=0A<DIV dir=3Dltr
alig=
n=3Dleft><SPAN class=3D146023221-07032008><FONT face=3DArial
=0D=0Acolor=3D=
#0000ff size=3D2>Compound index looks something like
=0D=0Athis</FONT></SPA=
N></DIV><SPAN class=3D146023221-07032008>=0D=0A<DIV dir=3Dltr
align=3Dleft>=
<BR><FONT face=3DArial color=3D#0000ff size=3D2>CREATE INDEX
=0D=0Aidx_bigt=
able_destorig </FONT></DIV>=0D=0A<DIV dir=3Dltr align=3Dleft><FONT
fa=
ce=3DArial color=3D#0000ff size=3D2>ON <SPAN
=0D=0Aclass=3D146023221-0=
7032008>bigtable</SPAN> USING btree<BR> (<SPAN
=0D=0Aclass=3D14=
6023221-07032008>originid</SPAN>, <SPAN
=0D=0Aclass=3D146023221-070320=
08>destinationid</SPAN>);</FONT></DIV>=0D=0A<DIV><FONT face=3DArial color=
=3D#0000ff size=3D2></FONT> </DIV>=0D=0A<DIV><FONT size=3D+0><SPAN
cla=
ss=3D146023221-07032008><FONT face=3DArial color=3D#0000ff
=0D=0Asize=3D2>A=
lso if the compound represents a unique record, you will want to make =0D=
=0Asure you create either a unique or primary key index on that=2E =
=0D=0AThe</FONT></SPAN></FONT></DIV>=0D=0A<DIV><FONT size=3D+0><SPAN
class=
=3D146023221-07032008><FONT face=3DArial color=3D#0000ff
=0D=0Asize=3D2>pla=
nner uses that information=2E For example if it is unique in your small
=0D=
=0Atable - you would setup the index
like</FONT></SPAN></FONT></DIV>=0D=0A<=
DIV><FONT size=3D+0><SPAN class=3D146023221-07032008><FONT face=3DArial
col=
or=3D#0000ff =0D=0Asize=3D2>CREATE UNIQUE INDEX
idx_smalltable_destorig&nbs=
p; </FONT>=0D=0A<DIV dir=3Dltr align=3Dleft><FONT face=3DArial
color=3D#000=
0ff size=3D2>ON <SPAN
=0D=0Aclass=3D146023221-07032008>smalltable</SPA=
N> USING btree (<SPAN
=0D=0Aclass=3D146023221-07032008>originid<=
/SPAN>, <SPAN
=0D=0Aclass=3D146023221-07032008>destinationid</SPAN>);<=
SPAN =0D=0Aclass=3D146023221-07032008> </SPAN></FONT></DIV>=0D=0A<DIV
=
dir=3Dltr align=3Dleft><FONT face=3DArial color=3D#0000ff size=3D2><SPAN =
=0D=0Aclass=3D146023221-07032008> </SPAN></DIV></FONT></SPAN></FONT><F=
ONT =0D=0Asize=3D+0></DIV>=0D=0A<DIV dir=3Dltr align=3Dleft><BR><FONT
face=
=3DArial color=3D#0000ff
=0D=0Asize=3D2></FONT></DIV></FONT></SPAN>=0D=0A<D=
IV dir=3Dltr align=3Dleft><SPAN class=3D146023221-07032008><FONT
face=3DAri=
al =0D=0Acolor=3D#0000ff size=3D2>Which version of PostgreSQL are you
using=
by the way? =0D=0ASo many changes in the planner have been made
betw=
een 8 and 8=2E3 that it would be =0D=0Ahelpful to know
this=2E</FONT></SPAN=
></DIV>=0D=0A<DIV dir=3Dltr align=3Dleft><SPAN
class=3D146023221-07032008><=
FONT face=3DArial =0D=0Acolor=3D#0000ff
size=3D2></FONT></SPAN> </DIV>=
=0D=0A<DIV dir=3Dltr align=3Dleft><SPAN class=3D146023221-07032008><FONT
fa=
ce=3DArial =0D=0Acolor=3D#0000ff size=3D2>Did you vacuum analyze before
han=
d to make sure the planner =0D=0Ahas up to date
stats?</FONT></SPAN></DIV>=
=0D=0A<DIV dir=3Dltr align=3Dleft><SPAN class=3D146023221-07032008><FONT
fa=
ce=3DArial =0D=0Acolor=3D#0000ff
size=3D2></FONT></SPAN> </DIV>=0D=0A<=
DIV dir=3Dltr align=3Dleft><SPAN class=3D146023221-07032008><FONT
face=3DAr=
ial =0D=0Acolor=3D#0000ff size=3D2>Hope that
helps,</FONT></SPAN></DIV>=0D=
=0A<DIV dir=3Dltr align=3Dleft><SPAN class=3D146023221-07032008><FONT
face=
=3DArial =0D=0Acolor=3D#0000ff
size=3D2>Regina</FONT></SPAN></DIV>=0D=0A<DI=
V dir=3Dltr align=3Dleft><SPAN class=3D146023221-07032008><FONT
face=3DAria=
l
=0D=0Acolor=3D#0000ff></FONT></SPAN> </DIV></SPAN></FONT></FONT></DI=
V>=0D=0A<DIV dir=3Dltr align=3Dleft><FONT face=3DTahoma><FONT
size=3D2><SPA=
N =0D=0Aclass=3D146023221-07032008> </SPAN><STRONG>From:</STRONG>
=0D=
=0Apgsql-novice-owner@[EMAIL PROTECTED]
=0D=0A<B>On Behalf Of </B>Chigoy, Byron T<BR><B>Sent:</B> Friday,
=
March 07, 2008 3:19 =0D=0APM<BR><B>To:</B>
pgsql-novice@[EMAIL PROTECTED]
><B>Subject:</B> [NOVICE] =0D=0AOptimizing Query
(Index)<BR></FONT></FONT><=
BR></DIV>=0D=0A<DIV></DIV>=0D=0A<DIV class=3DSection1>=0D=0A<P
class=3DMsoN=
ormal><FONT face=3DArial size=3D2><SPAN =0D=0Astyle=3D"FONT-SIZE: 10pt;
FON=
T-FAMILY: Arial">Hi,<o:p></o:p></SPAN></FONT></P>=0D=0A<P
class=3DMsoNormal=
><FONT face=3DArial size=3D2><SPAN =0D=0Astyle=3D"FONT-SIZE: 10pt;
FONT-FAM=
ILY: Arial"><o:p> </o:p></SPAN></FONT></P>=0D=0A<P
class=3DMsoNormal><=
FONT face=3DArial size=3D2><SPAN =0D=0Astyle=3D"FONT-SIZE: 10pt;
FONT-FAMIL=
Y: Arial">I have two tables; table1 has 3500 =0D=0Arecords (small table)
an=
d table2 (bigtable) has 25 million
=0D=0Arecords=2E<o:p></o:p></SPAN></FONT=
></P>=0D=0A<P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN
=0D=0Asty=
le=3D"FONT-SIZE: 10pt; FONT-FAMILY:
Arial"><o:p> </o:p></SPAN></FONT><=
/P>=0D=0A<P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN
=0D=0Astyle=
=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">I need to Inner Join the two
table=
s =0D=0Abased on two columns in each
table=2E<o:p></o:p></SPAN></FONT></P>=
=0D=0A<P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN
=0D=0Astyle=3D=
"FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>=
=0D=0A<P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN
=0D=0Astyle=3D=
"FONT-SIZE: 10pt; FONT-FAMILY: Arial">My query reads (I have changed the =
=0D=0Aliteral names here for easier
reading):<o:p></o:p></SPAN></FONT></P>=
=0D=0A<P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN
=0D=0Astyle=3D=
"FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>=
=0D=0A<P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN
=0D=0Astyle=3D=
"FONT-SIZE: 10pt; FONT-FAMILY: Arial">SELECT sm=2Erecordid, sm=2Eareaid, =
=0D=0Abg=2Edistance<o:p></o:p></SPAN></FONT></P>=0D=0A<P
class=3DMsoNormal>=
<FONT face=3DArial size=3D2><SPAN =0D=0Astyle=3D"FONT-SIZE: 10pt;
FONT-FAMI=
LY: Arial">FROM smalltable sm =0D=0A<o:p></o:p></SPAN></FONT></P>=0D=0A<P
c=
lass=3DMsoNormal style=3D"TEXT-INDENT: 0=2E5in"><FONT face=3DArial
size=3D2=
><SPAN =0D=0Astyle=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">INNER JOIN
bigta=
ble bg ON =0D=0A(bg=2Eoriginid=3Dsm=2Eoriginid AND
=0D=0Abg=2Edetinationid=
=3Dsm=2Edetinationid)<o:p></o:p></SPAN></FONT></P>=0D=0A<P
class=3DMsoNorma=
l><FONT face=3DArial size=3D2><SPAN =0D=0Astyle=3D"FONT-SIZE: 10pt;
FONT-FA=
MILY: Arial">WHERE
=0D=0Asm=2Esomecondition>0<o:p></o:p></SPAN></FONT></=
P>=0D=0A<P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN
=0D=0Astyle=
=3D"FONT-SIZE: 10pt; FONT-FAMILY:
Arial"><o:p> </o:p></SPAN></FONT></P=
>=0D=0A<P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN =0D=0Astyle=
=3D"FONT-SIZE: 10pt; FONT-FAMILY: Arial">I have tried indexing the
originid=
=0D=0Aand destinationid columns in both tables, and indexed the
somecondit=
ion column =0D=0Abut my query is still running extraordinarily
slow=2E =
; I looked at the query =0D=0Aplan, and it seems that my query plan only
re=
ferences the index on the =0D=0Asomecondition column=2E I need to
mak=
e this run much faster, any
=0D=0Atips?<o:p></o:p></SPAN></FONT></P>=0D=0A<=
P class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN
=0D=0Astyle=3D"FONT-S=
IZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT></P>=0D=0A<P
=
class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN
=0D=0Astyle=3D"FONT-SIZ=
E: 10pt; FONT-FAMILY: Arial">Regards,<o:p></o:p></SPAN></FONT></P>=0D=0A<P
=
class=3DMsoNormal><FONT face=3DArial size=3D2><SPAN
=0D=0Astyle=3D"FONT-SIZ=
E: 10pt; FONT-FAMILY:
Arial">B<o:p></o:p></SPAN></FONT></P></DIV></BODY></H=
TML>=0D=0A=0D=0A
------_=_NextPart_001_01C8809C.43A6353A--
|
