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

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