pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kate Collins <klcollins@wsicorp.com>
To: postgresql news group <pgsql-sql@postgresql.org>
Subject: SQL command speed
Date: Thu, 18 May 2000 15:11:27 -0400
Message-ID: <3924405E.15014E6B@wsicorp.com> (raw)

I am running PostgreSQL 7.0 on Red Hat Linux 6.2.  I am fairly new to
using PostgreSQL.  I am in the process of comparing performance with an
Oracle data base.  I have converted and populated several tables from
Oracle to PostgreSQL.

To test the speed of the data bases, I wrote the following script using
the PERL DBI:

--- Start Script 1 ---
    #!/bin/perl
    use DBI;
    $dbh = DBI->connect("dbi:Pg:dbname=pbi", "ntm", "", { RaiseError =>
1, AutoCommit => 0 })
        or die "Can't connect to PGSQL";

    $sql = "SELECT notam_id, TO_CHAR(full_issue_date, 'DD-MON-YYYY
HH24:MI')
        FROM notam_details
        WHERE
          item_a = 'EGKB' OR
          item_a = 'EGDM' OR
          item_a = 'EGHH' OR
          item_a = 'EGGD' OR
          item_a = 'EGVN' OR
          item_a = 'EGFF' OR
          item_a = 'EGDC' OR
          item_a = 'EGTC' OR
          item_a = 'EGDR' OR
          item_a = 'EGTE' OR
          item_a = 'EGLF' OR
          item_a = 'EGTG' OR
          item_a = 'EGBJ' OR
          item_a = 'EGLC' OR
          item_a = 'EGKK' OR
          item_a = 'EGLL' OR
          item_a = 'EGSS' OR
          item_a = 'EGGW' OR
          item_a = 'EGMD' OR
          item_a = 'EGDL' OR
          item_a = 'EGUM' OR
          item_a = 'EGHD' OR
          item_a = 'EGHE' OR
          item_a = 'EGKA' OR
          item_a = 'EGHI' OR
          item_a = 'EGMC' OR
          item_a = 'EGDG' OR
          item_a = 'EGFH' OR
          item_a = 'EGDY' OR
          item_a = 'EGJA' OR
          item_a = 'EGJB' OR
          item_a = 'EGJJ'";

    $sth = $dbh->prepare( $sql);
    $sth->execute();
    $result = $sth->fetchall_arrayref();
    foreach (@{$result})
      {
            ($id, $date) = @$_;
            print "$id:$date\n";
      }
--- End Script 1 ---

When I ran it it took 12 seconds with PostgreSQL and 1 second in Oracle.

I then went through several steps using vacuum, building indexes, etc,
but I did not see much of a performance improvement.  Then I used
explain, and noticed that the query was not using the indexes I created.
I did some experimentation, and if the WHERE clause had one or two items
it would use the index; more and it would not.

So I rewrote the script to do multiple small select queries instead of
one big select query.  The new script looked like this:

--- Start Script 2 ---
#!/bin/perl

use DBI;

$dbh = DBI->connect("dbi:Pg:dbname=pbi", "ntm", "",
    { RaiseError => 1, AutoCommit => 0 })
  or die "Can't connect to PGSQL";

$sql = "SELECT notam_id, TO_CHAR(full_issue_date, 'DD-MON-YYYY HH24:MI')

  FROM notam_details
  WHERE item_a = ?";

$sth = $dbh->prepare( $sql);

@stations = (EGKB, EGDM, EGHH, EGGD, EGVN, EGFF, EGDC, EGTC, EGDR, EGTE,
EGLF, EGTG, EGBJ, EGLC, EGKK, EGLL, EGSS, EGGW, EGMD, EGDL, EGUM, EGHD,
EGHE, EGKA, EGHI, EGMC, EGDG, EGFH, EGDY, EGJA, EGJB, EGJJ);
foreach (@stations)
 {
    $sth->bind_param( 1, $_);
    $sth->execute();
    $result = $sth->fetchall_arrayref();
    foreach $s (@{$result})
    {
        ($id, $date) = @$s;
        print "$id:$date\n";
     }
 }
--- End Script 2 ---

The result was the execution time of the script dropped to 1 second
using PostgreSQL!

At first I thought it was a feature of the PERL DBI, but I ran the same
queries using psql, and I got similar results.  I also ran some timing
checks of the PERL code and 99% of the execution time of the Script 1 is
being spent in the "execute" statement.

I have looked through the documentation of PostgreSQL, and I can find no
explanation for this.  I was curious if this is a know issue and thus is
the proper way to create SELECT statements in PostgreSQL?

Kate Collins

BTW, Script 2 takes a little longer to run on the Oracle system, circa
1.3 seconds.

--
=================================================
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: <3924405E.15014E6B@wsicorp.com>
Permalink:  ../3924405E.15014E6B@wsicorp.com/
Also on:    postgresql.org/message-id/3924405E.15014E6B@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
  Subject: Re: SQL command speed
  In-Reply-To: <3924405E.15014E6B@wsicorp.com>
  References: <3924405E.15014E6B@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