SQL command speed - Mailing list pgsql-sql

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