Received: from www2.intellicast.com ([208.194.150.24]) by hub.org (8.9.3/8.9.3) with SMTP id JAA65016 for ; Fri, 19 May 2000 09:22:06 -0400 (EDT) (envelope-from klcollins@wsicorp.com) Received: from wsicorp.com (ravens.wsicorp.com [147.81.80.26]) by www2.intellicast.com (950413.SGI.8.6.12/950213.SGI.AUTOCF) via ESMTP id JAA12827; Fri, 19 May 2000 09:20:55 -0400 Message-ID: <39253FB7.39516F9@wsicorp.com> Date: Fri, 19 May 2000 09:20:55 -0400 From: Kate Collins Organization: Weather Services International X-Mailer: Mozilla 4.72 [en] (WinNT; I) X-Accept-Language: en MIME-Version: 1.0 To: Tom Lane CC: postgresql news group Subject: Re: SQL command speed References: <3924405E.15014E6B@wsicorp.com> <25357.958686476@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200005/78 Tom, Thank you for your reply. The table I am using has 114600 total rows. The full query returns 1129 rows. Right now the table is static, i.e. I am not modifying it while I am running these tests. Here are the results of the EXPLAIN with the different numbers of OR's. ---QUERY 1, returns 1129 rows--- pbi=> explain pbi-> SELECT notam_id, TO_CHAR(full_issue_date, 'DD-MON-YYYY HH24:MI') pbi-> FROM notam_details pbi-> WHERE pbi-> item_a = 'EGKB' OR item_a = 'EGDM' OR item_a = 'EGHH' OR pbi-> item_a = 'EGGD' OR item_a = 'EGVN' OR item_a = 'EGFF' OR pbi-> item_a = 'EGDC' OR item_a = 'EGTC' OR item_a = 'EGDR' OR pbi-> item_a = 'EGTE' OR item_a = 'EGLF' OR item_a = 'EGTG' OR pbi-> item_a = 'EGBJ' OR item_a = 'EGLC' OR item_a = 'EGKK' OR pbi-> item_a = 'EGLL' OR item_a = 'EGSS' OR item_a = 'EGGW' OR pbi-> item_a = 'EGMD' OR item_a = 'EGDL' OR item_a = 'EGUM' OR pbi-> item_a = 'EGHD' OR item_a = 'EGHE' OR item_a = 'EGKA' OR pbi-> item_a = 'EGHI' OR item_a = 'EGMC' OR item_a = 'EGDG' OR pbi-> item_a = 'EGFH' OR item_a = 'EGDY' OR item_a = 'EGJA' OR pbi-> item_a = 'EGJB' OR item_a = 'EGJJ'; NOTICE: QUERY PLAN: Seq Scan on notam_details (cost=0.00..13420.40 rows=26230 width=12) EXPLAIN --- QUERY 2, returns 11 rows --- pbi=> explain pbi-> SELECT notam_id, TO_CHAR(full_issue_date, 'DD-MON-YYYY HH24:MI') pbi-> FROM notam_details pbi-> WHERE pbi-> item_a = 'EGKB'; NOTICE: QUERY PLAN: Index Scan using notam_details_item_a on notam_details (cost=0.00..2739.57 rows=927 width=12) EXPLAIN --- QUERY 3, returns 11 rows --- pbi=> explain pbi-> SELECT notam_id, TO_CHAR(full_issue_date, 'DD-MON-YYYY HH24:MI') pbi-> FROM notam_details pbi-> WHERE pbi-> item_a = 'EGKB' OR item_a = 'EGDM'; NOTICE: QUERY PLAN: Seq Scan on notam_details (cost=0.00..4820.90 rows=1847 width=12) EXPLAIN --- I have run VACUUM ANALYZE NOTAM_DETAILS and created an index on the item_a column. I have only been using PostgreSQL for about a week, so all of this is pretty new to me. I don't 100% understand how all of this works yet, so any insight you can provide will be appreciated. Kate Collins Tom Lane wrote > Kate Collins writes: > > I did some experimentation, and if the WHERE clause had one or two items > > it would use the index; more and it would not. > > Kate, it is reasonable behavior for the planner to stop using > indexscans when there are enough OR clauses. Each OR clause requires > a separate indexscan and so eventually it'll be cheaper to just do one > sequential scan over the whole table. What we appear to have here is > a case of misestimation of the relative costs of index and sequential > scans, leading the planner to switch too soon. Next question is why > the misestimation. It's difficult to say anything without seeing > your EXPLAIN results for different numbers of OR clauses. Also, how > big is the table (how many rows) and how many rows do you actually > get from the query? > > regards, tom lane -- ================================================= Katherine (Kate) L. Collins Senior Software Engineer/Meteorologist Weather Services International (WSI Corporation) 4 Federal Street Billerica, MA 01821 EMAIL: klcollins@wsicorp.com PHONE: (978) 670-5110 FAX: (978) 670-5100 http://www.intellicast.com