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
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