Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id CD5C2476501; Tue, 7 Jan 2003 11:56:53 -0500 (EST) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id 6159B4762C5; Tue, 7 Jan 2003 11:56:52 -0500 (EST) Received: from matrix.gatewaynet.com (matrix.gatewaynet.com [217.19.69.50]) by matrix.gatewaynet.com (8.12.5/8.12.5) with ESMTP id h07L5NXR009492; Tue, 7 Jan 2003 19:05:23 -0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.5/8.12.5/Submit) with ESMTP id h07L5Mh4009488; Tue, 7 Jan 2003 19:05:22 -0200 Date: Tue, 7 Jan 2003 19:05:22 -0200 (GMT+2) From: Achilleus Mantzios To: Tom Lane Cc: Stephan Szabo , , , Subject: Re: [SQL] [PERFORM] 7.3.1 index use / performance In-Reply-To: <13843.1041956988@sss.pgh.pa.us> Message-ID: MIME-Version: 1.0 Content-Type: MULTIPART/MIXED; BOUNDARY="333001285-1824804068-1041973522=:9324" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/238 X-Sequence-Number: 35359 This message is in MIME format. The first part should be readable text, while the remaining parts are likely unreadable without MIME-aware tools. Send mail to mime@docserver.cac.washington.edu for more info. --333001285-1824804068-1041973522=:9324 Content-Type: TEXT/PLAIN; charset=US-ASCII On Tue, 7 Jan 2003, Tom Lane wrote: > Achilleus Mantzios writes: > > About the stats on these 3 columns i get: > > Does 7.2 generate the same stats? (minus the schemaname of course) Not absolutely but close: (See attachment) > > Also, I would like to see the results of these queries on both versions, > so that we can see what the planner thinks the index selectivity is: > > EXPLAIN ANALYZE select * from noon where > v_code='4500'; > > EXPLAIN ANALYZE select * from noon where > report_date between '2002-01-07' and '2003-01-07'; > On 7.3.1 (On a FreeBSD) ======================= dynacom=# EXPLAIN ANALYZE select * from noon where v_code='4500'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Index Scan using noonf_vcode on noon (cost=0.00..3066.64 rows=829 width=1974) (actual time=2.02..1421.14 rows=792 loops=1) Index Cond: (v_code = '4500'::character varying) Total runtime: 1424.82 msec (3 rows) dynacom=# EXPLAIN ANALYZE select * from noon where report_date between '2002-01-07' and '2003-01-07'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Index Scan using noonf_date on noon (cost=0.00..15919.50 rows=11139 width=1974) (actual time=2.05..13746.17 rows=7690 loops=1) Index Cond: ((report_date >= '2002-01-07'::date) AND (report_date <= '2003-01-07'::date)) Total runtime: 13775.48 msec (3 rows) On 7.2.3 (Linux) ================== dynacom=# EXPLAIN ANALYZE select * from noon where v_code='4500'; NOTICE: QUERY PLAN: Index Scan using noonf_vcode on noon (cost=0.00..3043.45 rows=827 width=1974) (actual time=19.59..927.06 rows=792 loops=1) Total runtime: 928.86 msec dynacom=# EXPLAIN ANALYZE select * from noon where report_date between '2002-01-07' and '2003-01-07'; NOTICE: QUERY PLAN: Index Scan using noonf_date on noon (cost=0.00..16426.45 rows=11958 width=1974) (actual time=29.64..8854.05 rows=7690 loops=1) Total runtime: 8861.90 msec EXPLAIN > regards, tom lane > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr --333001285-1824804068-1041973522=:9324 Content-Type: TEXT/PLAIN; charset=US-ASCII; name="querystats_on_7.2.3" Content-Transfer-Encoding: BASE64 Content-ID: Content-Description: Content-Disposition: attachment; filename="querystats_on_7.2.3" IHRhYmxlbmFtZSB8ICAgYXR0bmFtZSAgIHwgbnVsbF9mcmFjIHwgYXZnX3dp ZHRoIHwgbl9kaXN0aW5jdCB8ICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgbW9zdF9jb21tb25fdmFscyAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICB8ICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIG1vc3Rf Y29tbW9uX2ZyZXFzICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgIHwgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgIGhpc3RvZ3JhbV9ib3VuZHMgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICB8IGNvcnJlbGF0aW9uIA0KLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0t LSstLS0tLS0tLS0tLSstLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0rLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLSstLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0rLS0tLS0tLS0tLS0tLQ0KIG5v b24gICAgICB8IHZfY29kZSAgICAgIHwgICAgICAgICAwIHwgICAgICAgICA4 IHwgICAgICAgIDEwOSB8IHs0NTUwLDQ2MzAsNDY1MCw0ODAwLDQ1MjAsNDc3 MCw0NjkwLDQ2MjAsNDYxMCw0NTYwfSAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICB8IHswLjAy NzMzMzMsMC4wMjYzMzMzLDAuMDI2LDAuMDI1NjY2NywwLjAyNTMzMzMsMC4w MjUzMzMzLDAuMDI0NjY2NywwLjAyMjY2NjcsMC4wMjIzMzMzLDAuMDIyfSAg ICAgICAgIHwgezEwMzAsMzIxMCw0MzYwLDQ1MDAsNDU3MCw0NjcwLDQ3NDAs NDgyMCw0ODcwLDQ5NDAsNjAyMH0gICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICB8ICAgLTAuMjYwMzc3DQogbm9vbiAgICAgIHwgcmVwb3J0X2RhdGUgfCAg ICAgICAgIDAgfCAgICAgICAgIDQgfCAgICAgICAzNDAyIHwgezE5OTktMDEt MjIsMjAwMC0xMi0yNiwxOTk4LTA5LTI5LDE5OTgtMTAtMTEsMTk5OS0wMi0y NCwxOTk5LTA1LTE5LDE5OTktMDktMDgsMTk5OS0wOS0xMywyMDAwLTAxLTE5 LDIwMDAtMDItMDN9IHwgezAuMDAyLDAuMDAyLDAuMDAxNjY2NjcsMC4wMDE2 NjY2NywwLjAwMTY2NjY3LDAuMDAxNjY2NjcsMC4wMDE2NjY2NywwLjAwMTY2 NjY3LDAuMDAxNjY2NjcsMC4wMDE2NjY2N30gfCB7IjAwMDEtMTEtMDcgQkMi LDE5OTQtMTAtMjIsMTk5Ni0wNC0wNSwxOTk3LTA2LTE5LDE5OTgtMDctMzEs MTk5OS0wNC0wMSwxOTk5LTEyLTE1LDIwMDAtMDktMjksMjAwMS0wNS0zMSwy MDAyLTAyLTA2LDIwMDMtMDEtMDJ9IHwgICAtMC44MjE2MjcNCiBub29uICAg ICAgfCByb3RhdGlvbiAgICB8ICAgICAgICAgMCB8ICAgICAgICAxMyB8ICAg ICAgICAgIDYgfCB7Ik5PT04gICAgICIsIlBPUlQgTE9HICIsIkFSUklWQUwg ICIsREVQQVJUVVJFLCJTRUEgICAgICAiLCJORVhUIFBPUlQifSAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgfCB7MC40NzgsMC4y NTM2NjcsMC4xMzgzMzMsMC4xMjE2NjcsMC4wMDYsMC4wMDIzMzMzM30gICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICB8ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgfCAg ICAwLjE0NzgyMg0KKDMgcm93cykNCg0K --333001285-1824804068-1041973522=:9324--