RE: [GENERAL] Query time is too long for netscape - Mailing list pgsql-general

Without knowing the distribution of records in the transaction table it is
difficult to relate the schema to the actual data.  Do you know what a
typical query will be like?  i.e.  How is a typical date range and
service_type query going to limit the number of rows returned?  Will a one
month date range and a particular service type limit the return set to a
couple rows, a couple hundred, a couple of hundred thousand?

The first thing that caught my eye is that you are not limiting the query
results to an individual customer's ID so conceivably you could be returning
a large majority of half a million rows.  Could this be the problem?

Phil Culberson
DAT Services

-----Original Message-----
From: Chairudin Sentosa Harjo [mailto:chai@prima.net.id]
Sent: Tuesday, January 25, 2000 1:12 AM
To: Mark Jewiss
Cc: pgsql-general
Subject: Re: [GENERAL] Query time is too long for netscape


Mark Jewiss wrote:
>
> Hello,
>
> On Tue, 25 Jan 2000, Chairudin Sentosa Harjo wrote:
>
> > The query looks like this:
> > select a.custnum, b.fullname, b.address, a.usages from
> > Detail a, Customer b
> > where a.custnum=b.custnum;
>
> Have you got any keys setup on these tables? That would help the query a
> lot.
>
> Mail out your table definitions (and the script) and then we can have a
> look.

I do have a lot of index combinations to help speed up the query,
however
it still takes too long. Here is the script and the table definitions.

SCRIPT
$sql = "select a.pin, b.first_name, b.last_name,
        start_time, duration,
        country_code, area_code, phone_number,
        a.service_type, total_units
        from tbs_billing_record a, ibs_subscriber b
        where a.pin=b.pin
        and date(start_time) between '$begindate' and '$enddate'
        and a.service_type='$service_type'
        order by $sortby1,$sortby2";

Pg::doQuery($conn,$sql,\@bilrec);

The values of $begindate, $enddate, $service_type, $sortby1, $sortby2
are given by user, through HTML form using CGI script.


TABLE DEFINITION
create sequence tbs_br_seq
       increment 1
       minvalue 0000000001
       start 1;

create table tbs_billing_record
(
 tbs_br_seq           int DEFAULT nextval('tbs_br_seq') NOT NULL,
 pin                  varchar(128) NOT NULL,
 start_time           datetime,
 duration             integer,
 service_code         varchar(2),
 country_code         varchar(3),
 area_code            varchar(3),
 phone_number         varchar(24),
 service_type         varchar(1),
 total_units          float,

);

create index tbs_br_pin_idx on tbs_billing_record (pin);
create index tbs_br_start_time_idx on tbs_billing_record (start_time);
create index tbs_br_duration_idx on tbs_billing_record (duration);
create index tbs_br_country_code_idx on tbs_billing_record
(country_code);
create index tbs_br_total_units_idx on tbs_billing_record (total_units);
create index tbs_br_pin_start_time_idx on tbs_billing_record (pin,
start_time);
create index tbs_br_pin_duration_idx on tbs_billing_record (pin,
duration);
create index tbs_br_pin_total_units_idx on tbs_billing_record (pin,
total_units);
create index tbs_br_start_time_duration_idx on tbs_billing_record
(start_time, duration);
create index tbs_br_tbsbrseq_idx on tbs_billing_record (tbs_br_seq);
create index tbs_br_pinstarttimeservicetype_idx on tbs_billing_record
(pin,start_time,service_type);
create index tbs_br_starttimeservicetype_idx on tbs_billing_record
(start_time,service_type);


create table ibs_subscriber
(
 id            integer,
 last_name     varchar(30),
 first_name    varchar(20),
 mi_name       varchar(1),
 password      varchar(20),
 pin           varchar(128),
);

create index ibs_subs_id_idx on ibs_subscriber (id);
create index ibs_subs_last_name_idx on ibs_subscriber (last_name);
create index ibs_subs_first_name_idx on ibs_subscriber (first_name);
create index ibs_subs_password_idx on ibs_subscriber (password);
create index ibs_subs_last_first_name_idx on ibs_subscriber (last_name,
first_name);
create index ibs_subs_id_password_idx on ibs_subscriber (id, password);
create index ibs_subs_pin_idx on ibs_subscriber (pin);
create index ibs_subs_pin_flname_idx on ibs_subscriber
(pin,first_name,last_name);


To print out the detail I use this (part of the long script):

   foreach $record (@bilrec)
     {
      ($pin,$first_name,$last_name,$start_time,$duration,
       $country_code,$area_code,$phone_number,$service_type,
       $total_units)=@$record;

      $new_duration  = ($duration/60);
      ($front,$back) = split(/\./,$new_duration);
      $new_back = substr($back,0,2);
      $new_duration = $front.'.'.$new_back;

      $no = $no + 1;
      $fullname=$first_name.' '.$last_name;
      push(@rows,
           td([$no,$pin,$fullname,$start_time,center($new_duration),
               $country_code,$area_code,$phone_number,
               $service_type,
               $total_units]));
     }

   print table
     ({-border=>'', -cellspacing=>3, -cellpadding=>3},
      TR(\@rows)


Regards,
Chai

************