From mark@vestnesis.lv Thu Nov 22 09:46:50 2001 Received: from lv-fw ([159.148.47.176]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAM9kkm28076 for ; Thu, 22 Nov 2001 04:46:50 -0500 (EST) (envelope-from mark@vestnesis.lv) Received: (from mail@localhost) by lv-fw (8.11.1/8.11.1) id fAMAqvs47106 for pgsql-admin@postgresql.org; Thu, 22 Nov 2001 12:52:57 +0200 (EET) (envelope-from mark@vestnesis.lv) X-Authentication-Warning: lv-fw: mail set sender to mark@vestnesis.lv using -f Received: from 10.11.12.10 by 64.49.215.8 id E_pP1xVN ; 22 Nov 2001 12:52:55 EET Received: from varde (varde [10.11.12.30]) by vestnesis.lv (8.11.2/x.y.z/Who Cares x.y.z-a.b) with ESMTP id fAM9g0b02543 for ; Thu, 22 Nov 2001 11:42:03 +0200 From: "Martins Zarins" To: pgsql-admin@postgresql.org Date: Thu, 22 Nov 2001 11:37:47 +0200 MIME-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Subject: indexes Reply-to: mark@vestnesis.lv Message-ID: <3BFCE38B.29700.A0ED3F1@localhost> X-mailer: Pegasus Mail for Win32 (v3.12c) X-Archive-Number: 200111/107 X-Sequence-Number: 2632 Hi All! Is REINDEX TABLE xxx; the same as DROP INDEX ... ; CREATE INDEX ... on xxx (..); or there are differences? Mark From jkanter@virginia.edu Fri Feb 28 16:55:25 2003 X-Original-To: pgsql-admin@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 7AD704761B5 for ; Fri, 28 Feb 2003 11:55:25 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 69442-04 for ; Fri, 28 Feb 2003 11:55:13 -0500 (EST) Received: from mail.virginia.edu (mail.Virginia.EDU [128.143.2.9]) by postgresql.org (Postfix) with SMTP id 2B5E347620A for ; Fri, 28 Feb 2003 10:41:34 -0500 (EST) Received: from smtp.mail.virginia.edu by mail.virginia.edu id ab12935; 28 Feb 2003 10:41 EST Received: from jlk3xpc (d-128-19-222.bootp.Virginia.EDU [128.143.19.222]) by smtp.mail.Virginia.EDU (8.11.6/8.11.6) with SMTP id h1SFfaN23118 for ; Fri, 28 Feb 2003 10:41:36 -0500 Message-ID: <006d01c2df40$a9f34a70$de138f80@virginia.edu> From: Jodi Kanter To: Postgres Admin List Subject: indexes Date: Fri, 28 Feb 2003 10:47:13 -0500 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_006A_01C2DF16.C0FEBDF0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/432 X-Sequence-Number: 7630 This is a multi-part message in MIME format. ------=_NextPart_000_006A_01C2DF16.C0FEBDF0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have three tables that are queried frequently together. I have indexes on= the PK values in each table as these are the fields that are typically use= d in the where clauses. Do I need to also have indexes on the corresponding= FK values to improve performance? Thanks, Jodi _______________________________ Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 jkanter@virginia.edu =20 =20 =20 ------=_NextPart_000_006A_01C2DF16.C0FEBDF0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
I have three tables that are queried=20 frequently together. I have indexes on the PK values in each table as these= are=20 the fields that are typically used in the where clauses. Do I need to also = have=20 indexes on the corresponding FK values to improve=20 performance?
Thanks,
Jodi

_______________________________
Jodi=20 L Kanter
BioInformatics Database Administrator
University of=20 Virginia
(434) 924-2846
jkanter@virginia.edu

 

 

 

<= /DIV> ------=_NextPart_000_006A_01C2DF16.C0FEBDF0-- From lea03@poczta.onet.pl Fri Nov 4 22:57:22 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 802A4D956C for ; Fri, 4 Nov 2005 18:57:22 -0400 (AST) 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 21019-07 for ; Fri, 4 Nov 2005 22:57:19 +0000 (GMT) X-Greylist: delayed 00:29:57.311395 by SQLgrey- Received: from smtp3.poczta.onet.pl (smtp3.poczta.onet.pl [213.180.130.29]) by svr1.postgresql.org (Postfix) with ESMTP id C7284D93CF for ; Fri, 4 Nov 2005 18:57:19 -0400 (AST) Received: from pg92.ruda-slaska.sdi.tpnet.pl ([80.49.37.92]:2568 "EHLO toshi") by ps3.test.onet.pl with ESMTP id ; Fri, 4 Nov 2005 23:26:55 +0100 Message-ID: <001c01c5e18e$dd549240$f800a8c0@toshi> From: =?windows-1250?Q?Leonia_Zaj=B9c?= To: Subject: INDEXes Date: Fri, 4 Nov 2005 23:26:55 +0100 MIME-Version: 1.0 Content-Type: multipart/related; type="multipart/alternative"; boundary="----=_NextPart_000_0018_01C5E197.3EB4E140" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.2180 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.816 required=5 tests=[EXTRA_MPART_TYPE=0.815, HTML_MESSAGE=0.001] X-Spam-Score: 0.816 X-Spam-Level: X-Archive-Number: 200511/45 X-Sequence-Number: 19418 This is a multi-part message in MIME format. ------=_NextPart_000_0018_01C5E197.3EB4E140 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0019_01C5E197.3EB4E140" ------=_NextPart_001_0019_01C5E197.3EB4E140 Content-Type: text/plain; charset="windows-1250" Content-Transfer-Encoding: quoted-printable Li=9CcieHi ! Can anybody say, HOW does the postgre sql query planer, or executer, = prepers the query plan, if i have several indexes on my table ? Best Regards Leo ------=_NextPart_001_0019_01C5E197.3EB4E140 Content-Type: text/html; charset="windows-1250" Content-Transfer-Encoding: quoted-printable Li=9Ccie
Hi !
 
Can anybody say, HOW does the postgre sql query = planer, or=20 executer,  prepers the query plan, if i have several indexes on my = table=20 ?
 
 
Best Regards
Leo

 

------=_NextPart_001_0019_01C5E197.3EB4E140-- ------=_NextPart_000_0018_01C5E197.3EB4E140 Content-Type: image/jpeg; name="Leaves Bkgrd.jpg" Content-Transfer-Encoding: base64 Content-ID: <001701c5e18e$dceb9740$f800a8c0@toshi> /9j/4AAQSkZJRgABAgEASABIAAD/7QZAUGhvdG9zaG9wIDMuMAA4QklNA+0AAAAAABAASAAAAAEA AQBIAAAAAQABOEJJTQPzAAAAAAAIAAAAAAAAAAA4QklNBAoAAAAAAAEAADhCSU0nEAAAAAAACgAB AAAAAAAAAAI4QklNA/UAAAAAAEgAL2ZmAAEAbGZmAAYAAAAAAAEAL2ZmAAEAoZmaAAYAAAAAAAEA MgAAAAEAWgAAAAYAAAAAAAEANQAAAAEALQAAAAYAAAAAAAE4QklNA/gAAAAAAHAAAP////////// //////////////////8D6AAAAAD/////////////////////////////A+gAAAAA//////////// /////////////////wPoAAAAAP////////////////////////////8D6AAAOEJJTQQIAAAAAAAQ AAAAAQAAAkAAAAJAAAAAADhCSU0ECQAAAAAEzwAAAAEAAACAAAAAgAAAAYAAAMAAAAAEswAYAAH/ 2P/gABBKRklGAAECAQBIAEgAAP/+ACdGaWxlIHdyaXR0ZW4gYnkgQWRvYmUgUGhvdG9zaG9wqCA0 LjAA/+4ADkFkb2JlAGSAAAAAAf/bAIQADAgICAkIDAkJDBELCgsRFQ8MDA8VGBMTFRMTGBEMDAwM DAwRDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAENCwsNDg0QDg4QFA4ODhQUDg4ODhQRDAwM DAwREQwMDAwMDBEMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwM/8AAEQgAgACAAwEiAAIRAQMR Af/dAAQACP/EAT8AAAEFAQEBAQEBAAAAAAAAAAMAAQIEBQYHCAkKCwEAAQUBAQEBAQEAAAAAAAAA AQACAwQFBgcICQoLEAABBAEDAgQCBQcGCAUDDDMBAAIRAwQhEjEFQVFhEyJxgTIGFJGhsUIjJBVS wWIzNHKC0UMHJZJT8OHxY3M1FqKygyZEk1RkRcKjdDYX0lXiZfKzhMPTdePzRieUpIW0lcTU5PSl tcXV5fVWZnaGlqa2xtbm9jdHV2d3h5ent8fX5/cRAAICAQIEBAMEBQYHBwYFNQEAAhEDITESBEFR YXEiEwUygZEUobFCI8FS0fAzJGLhcoKSQ1MVY3M08SUGFqKygwcmNcLSRJNUoxdkRVU2dGXi8rOE w9N14/NGlKSFtJXE1OT0pbXF1eX1VmZ2hpamtsbW5vYnN0dXZ3eHl6e3x//aAAwDAQACEQMRAD8A 6/v5hJJLhPYlA/glwklMpKVolP3pp7p++iSldvNKR3+SRS8klK+SU+CRSSUrv5JSOEo7pT2SUpKN EpMeJSJSUr4pJdkvjykp/9Dr+QkJIShKQT8E9iUEvJJLVJStfmkPw8UvglOsH70lK+PCUd0hwl8U lK8kvhwEvNL4JKUSkEtOEklKjxS0S58vNIpKUlB+SUa6duUoSU//0evTd0/YjhL569k9iUUkktJ8 0lKSBKR5S5SUrRLVIeSY68d0lL9vimGif+KXkkpWiXhHzS4S1RUoifIJaBKdPJL4oKV5/elOmiXw SnXQpKf/0uv14SOqQ8PuSPMp7EpLulKRn5JKUkPvT88pu89+ySlJQAl/rCRjg/JJS3h+Cf8AIkkf E/gkpXKQ8kvuSmPJJStO6RSS/KkpXhPdJKfH5JJKf//T69LjQpf6yl+RPYlf6hLzSB/3JdklK8+3 glz/ABKSUapKVPilx/sSB8PklISUqNP70vxCRSSUrRJKI80vNJSu6RS5S0+SSlJcaH70pCUJKf/U 6/4JDwS+CXKexK+KRMJaz8Eo7JKV3SidTwkkQkpRPyS7pSEklK5SS8+6SSla8pJtDpx5J0lLaJz/ ALwl240SJhJSo1+HZL8qXmlqR8UlP//V67wP4JylH3pTrHfunsSkuPNL8vZLySUrlJNyU6SlJdpS Snx7d0lK158UvMfclyNO6XCSlHRIJfxSSUpIpJQkpQ1SS1kSkkp//9br5180pHZKUpjT8U9iVyUu 2iXxCWqSldvBJLTsUuP4pKV2SA/2Jo08E6SlTokEuNEklKmEkw5TwOUlK8kkvwKRSUqfx4TpkuNB 80lP/9kAOEJJTQQGAAAAAAAHAAMBAQABAQD//gAnRmlsZSB3cml0dGVuIGJ5IEFkb2JlIFBob3Rv c2hvcKggNC4wAP/uACFBZG9iZQBkAAAAAAEDABADAgMGAAAAAAAAAAAAAAAA/9sAhAAKBwcHCAcK CAgKDwoICg8SDQoKDRIUEBASEBAUEQwMDAwMDBEMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwMDAwM AQsMDBUTFSIYGCIUDg4OFBQODg4OFBEMDAwMDBERDAwMDAwMEQwMDAwMDAwMDAwMDAwMDAwMDAwM DAwMDAwMDAz/wgARCADIAMgDAREAAhEBAxEB/8QAfgABAQEBAAAAAAAAAAAAAAAAAAECBgEBAQEB AAAAAAAAAAAAAAAAAAECAxABAAICAwEBAQEAAAAAAAAAAQARECEgMDFBAkASEQACAgIDAAIDAQEB AQAAAAAAAREhMUEQUWFxgZGxwaEi0RISAQAAAAAAAAAAAAAAAAAAAJD/2gAMAwEBAhEDEQAAAO04 bikKQpAUCpAUgWyKigVIpAAi0iUJGqghQCpLRI1QkQpagiLUAtiWKZtsgBSAFJZcVpBbZIW1JAUk tsgKkUAVYlIKsRakBSLUliWkFtSSFKQUJFpKQQ1bItkgFlzVkWhDSySLQEUi2ySLbKQQKublLaRC iipAoJF0kUJGiAhVtSQi6QsQAFBEtsgUAlVGbLAaJUjVFkSyyLpIWWWRagWoIUiGrZIFIUEBSAAR aiFWWI1UgAW2SAsssKCUiipFJLbIWxLELbBJaKEhSQqxm2pVkNEEFIAqrIWJRKsikQ1UEC1JZZVi USrEKLEFhSBDVSBFWWFJZZYhS1IAW1IqRSQoEC1FJVJFJF0kqrJCwokCgKspIAWpIW1M22KkUhVS KlIIVFokW2CFiKKQFCS2xAAUgWpBVgKRFqRCikFIpEKC2wjNVEXVkEQq2ySLUW2SBRUS5soAgqgE isrtIW2QCLRcpYhVWSKCFAsSrZJSFBC0gpFIUEAWsy1FUzFAtSC25LCkiggi2S1FpIWFUhJdWQKC BAAUiKssKCWWJbYktslIUyUssspKQhq2TNagsSyxJpYWpKsSLUC25Z1LAUlJZZYUgssslosgKCFI hRZVkBf/2gAIAQIAAQUAIvB43Di8bhF6Hh84srh86Xh5wOZzewxVdD7wOm4YXsOIcXoHrM1Lx87L xeXFR5XHF4OFc71weB0OQzXI5PXUeV9dS49PvTeXIcnocXzqV03lOlw4Hk4vFYeF4ZXK++8Vyeyp XOudwjn501m8HH5kIPO8ByZXA4E+4cJ1+SuusXPnJ43yvJkzUqXfCs3moPVUeF8w7L4BisOK4h0P vQuXoWf/2gAIAQMAAQUAcMviS4RzeLyS4Rw8jqrorBmuFdd5DjWLw5cV1L0vReDvDFQxXKsuPMOa zfA51ip8hK76wOb43KzX8Liug51/EuayS+Rwrg9FcTsZceN8CXyqpfZXC+dxxfA7jFx7GEcGXB11 hyub4Xi+VcbxfKo8CVj5wDhWaycSLLwcXJkOgl8F5s8x8vi9t32LiulwcmGHi5vNcL5EOLxOFcDs qViv4nj/AP/aAAgBAQABBQB3KK1LQ9B1W2jF78XcQDTN17Bmr1CXvxdyitRN6nsps3EIaLl2hWKh GiVPJ+Xe0BlQExUPFZbELusUEFly9u4bKUZQMbpqqi1EE8w2zcQvSm4XGbt8giaxe/SmXEubC9XB uaJW2VcJ5DcLpLmiBu5VzUqEWNk1B2T6tRKgBNRqW3aSyNSyVPJphG5uqAra/wCf02Mrfsqi5Uqb H47hRPv6LN1shufKI3Wp4nhYlMZutT/NR8FuJA/UEZaT8q4tEblg6nylZRYjKouVLhcQs9+1Ladw dBSEux/WtRu0YVaNghY4ojsBuUXGfm5VwoKx7C4XL3Re58LiDA2rC6Ze/taKZQpo3PES/Z5HT+bp LGA0JPI7gVKqPjU3ZcQumWkvcqo6Ft+lrPmrqXoVjTEQuwhWKLUw3C61EH9O0d/arHsChuWQlaib u3CWWEYfkjUslVDzYlM+toanksSkbYT8iS9sWJZYRnsq5e/Y7n0bmgu1CCTxFtI7l7Z8hVexLnsq 5e7lkPPJWnR/ot3ELqaIm/I+/l3tAZThtKlXCowGts3gES7hNxahbAoqoeLqF2gwsX38ui2IkVq9 CsdzcJuVtdiP5GimDSgu7fal4qoeem4Lfj+q/RWoCYqXr40wuos/IxALt9gVKJtjcLpJRAl1Ny7H FXNxN6on27xtiteT6lyowtbh5RZdK4TdF1YXTU1VDNBdqEu4x9+XumJZu1pKW9Rj6TyAMaIaKKik fH8wd0T7dTUoi6LIeOzyXEZe7qaI1NRowWxodMNKtg0yy/Volq1csIOkYk8jcKnr+jXw1jTGKyiN 1qbsGUW0z75FpG0uErZH9UEaZdN3PIlxRh+ZqbcUWI4fV15LbTXgu9M3QrA38XXy0al0E+t2DVRQ dMfC1QzbZuOwNkbq2eRDNt//2gAIAQICBj8AHH//2gAIAQMCBj8AHH//2gAIAQEBBj8AgghaIm++ MV2SSTJ50KLfRBHRR72RtEQekkpnnQot9EEELQoO+M0j4zx5xmHonJ6Si8mInohueHV9jimS/sp/ BesnpKLIIxw+uJmOz9Mt4EnXF0U4fEiapHpD3xPHpZWeJIWuLJRk/hGOLvo9McTMwJf5x2UuJyie f0KL7P4XkvJ6hLb1x+idk9FZ0Xksqy983ronTJR/rI0ecOiiyrP0OWT+CGRvQuxlCT3sUX2eFuij 9HyOcOyPwVh5Ksh48P8A562y6XZCr1CTyubK5yfI4wfBR+xfoon/AAUWtkf6LtFYGlbREcJ9Hmyh w76KI2LTRP5Jgg+eK+yiUeo7PCinHFqCe+HBOiNEaPT50Mtyxfvj0rJKqMk6I+zwrGx9PBDJeRQf sgvHHR/Bel/klOUyfwf+jjOkTt6474slYZ6RsSX2JHheDw8If0Q8n9Ieez4ou+jMHnRCZ7zWGTsZ Z1xTzwl3sjmBp/SJxx0YJVsT70WRw5Vd8x3hkTZOyetmbRZGivxxiCMoTj5LVlkdEJiivSHxars9 I2eMnZ4uPMnjHcJHq4umXZSlH8JQp/wgcX4yH+Sl9kHpGzx8TscqCSVokh0fOSiUUdvUmBa4hokp Vxk9RD+nxOxyoFURxD/PMvAl2Qvsnok+T4IL2Or7HFMl/YiCETvjqD+kPPZaI09lE95RZGirIJeh PRZKeSxVMiQiMEZIMQxMzkuuOjJEkcQj1Ek5Jynoql4Rh8fI4/HGBw+LP6eHfhDtekdEJl6yekov JOxdmMcUy/s7POz9Dl4JOhrehVfRkjOy8E7RKsspfZZTh9nRK4/0fR1HDKV9FcRkrAv2OVSwyZ4n D4rPXGaIzGxXZDyhTkr74+clEorifwK/khZEiE4YkQSvtMkU/nifwLfZ/CcPZWNkTPpZGxLbIE5t E/k84Z/zsmP+iXo7TPT0iD+FOCBn/JLJMcOU0xtk/jh96PdlEL8EYKox9n7Q+lokrBB8kkZIwQqY qvZiyehzoS6O+ysc5Op4XWyNEFmIjfMqhmJ8I+hdbLxw7nwV42UZyekY7PETB8E6JWDw/pEEctYj RVwTEEjg8I/0hI92T3ji8HjIbvs9QlviFkTxGUdrYqzgsnKeD0vJi9lYITtlo8Z2tirOCycp4P/Z ------=_NextPart_000_0018_01C5E197.3EB4E140-- From pgman@candle.pha.pa.us Fri Nov 4 23:08:26 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 62CAAD97D6 for ; Fri, 4 Nov 2005 19:08:26 -0400 (AST) 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 27741-09 for ; Fri, 4 Nov 2005 23:08:23 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from candle.pha.pa.us (candle.pha.pa.us [64.139.89.126]) by svr1.postgresql.org (Postfix) with ESMTP id 87FD5D93CF for ; Fri, 4 Nov 2005 19:08:24 -0400 (AST) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.11.6) id jA4N8Qw05731; Fri, 4 Nov 2005 18:08:26 -0500 (EST) From: Bruce Momjian Message-Id: <200511042308.jA4N8Qw05731@candle.pha.pa.us> Subject: Re: INDEXes In-Reply-To: <001c01c5e18e$dd549240$f800a8c0@toshi> To: =?windows-1250?Q?Leonia_Zaj=B9c?= Date: Fri, 4 Nov 2005 18:08:26 -0500 (EST) Cc: pgsql-admin@postgresql.org X-Mailer: ELM [version 2.4ME+ PL121 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Content-Type: text/plain; charset=UNKNOWN-8BIT X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.014 required=5 tests=[AWL=0.014] X-Spam-Score: 0.014 X-Spam-Level: X-Archive-Number: 200511/46 X-Sequence-Number: 19419 Leonia Zając wrote: > Li?cieHi ! > > Can anybody say, HOW does the postgre sql query planer, or executer, > prepers the query plan, if i have several indexes on my table ? You can look at EXPLAIN to see the index chosen. I have an "Internals through Pictures" PDF on my home page below that outlines how the decisions are made, plus there is the source code and various README files that go into details about the optimizer. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073