pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kate Collins <klcollins@wsicorp.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: postgresql news group <pgsql-sql@postgresql.org>
Subject: Re: SQL command speed
Date: Fri, 19 May 2000 09:20:55 -0400
Message-ID: <39253FB7.39516F9@wsicorp.com> (raw)
References: <3924405E.15014E6B@wsicorp.com>
	<25357.958686476@sss.pgh.pa.us>

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 <klcollins@wsicorp.com> 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





view thread (12+ messages)  latest in thread

Message-ID: <39253FB7.39516F9@wsicorp.com>
Permalink:  ../39253FB7.39516F9@wsicorp.com/
Also on:    postgresql.org/message-id/39253FB7.39516F9@wsicorp.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: klcollins@wsicorp.com, tgl@sss.pgh.pa.us
  Subject: Re: SQL command speed
  In-Reply-To: <39253FB7.39516F9@wsicorp.com>
  References: <3924405E.15014E6B@wsicorp.com> <25357.958686476@sss.pgh.pa.us> <39253FB7.39516F9@wsicorp.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox