Re: Resources - Mailing list pgsql-sql

Hi,

Please find the table schemas, queries and also explain on those
queries.

############### TABLES SCHEMA ##########################

/* -------------------------------------------------------- Table structure for table "subscribertab"
-------------------------------------------------------- */
CREATE TABLE "subscribertab" (  "custcode" int8 NOT NULL,  "netcode" int8 NOT NULL,  "custname" varchar(100) NOT NULL,
"address1"varchar(100) NOT NULL,  "address2" varchar(100),  "citycode" int8 NOT NULL,  "pincode" int8 NOT NULL,
"telephoneno"varchar(15),  "emailid" varchar(50),  "typeconnection" char(1) NOT NULL,  "pagerno" varchar(10),
"mobileno"varchar(10),  "notv" int8,  "nocomp" int8,  "userid" varchar(12),  "packagecode" int8,  "techcode" int8,
"cablesubamount"float8,  "esn" varchar(25),  "contactperson" varchar(50),  "rfctx" float8,  "rfcrx" float8,  "rfstx"
float8, "rfsrx" float8,  "latencygateway" float8,  "latencyrouter" float8,  "pitstop" float8,  "topsites" varchar(150),
"usagetime" varchar(100),  "typicalusage" varchar(100),  "activatedate" date,  CONSTRAINT "subscribertab_pkey" PRIMARY
KEY("custcode")
 
);


/* -------------------------------------------------------- Table structure for table "tickettab"
-------------------------------------------------------- */
CREATE TABLE "tickettab" (  "tokenid" varchar(25) NOT NULL,  "subcode" int8,  "techcode" int8,  "agentid" varchar(12)
NOTNULL,  "hmsupcode" int8,  "problemstmt" text,  "arrivaldate" date NOT NULL,  "arrivaltime" time NOT NULL,
"attendeddate"date,  "attendedtime" time,  "statuscode" int8,  "shiftcode" int8,  "catcode" int8,  "othercat"
varchar(50), "remarks" text,  "updatedby" varchar(12),  "routedby" varchar(12),  "routeddate" date,  "routedtime" time,
"solonline" char(1),  CONSTRAINT "tickettab_pkey" PRIMARY KEY ("tokenid")
 
);


/* -------------------------------------------------------- Table structure for table "ticketmultab"
-------------------------------------------------------- */
CREATE TABLE "ticketmultab" (  "tcode" int8 NOT NULL,  "tokenid" varchar(25) NOT NULL,  "techcode" int8,  "agentid"
varchar(12)NOT NULL,  "problemstmt" text,  "arrivaldate" date NOT NULL,  "arrivaltime" time NOT NULL,  "attendeddate"
date, "attendedtime" time,  "statuscode" int8,  "shiftcode" int8,  "catcode" int8,  "othercat" varchar(50),  "remarks"
text, "updatedby" varchar(12),  "routedby" varchar(12),  "routeddate" date,  "routedtime" time,  "solonline" char(1),
CONSTRAINT"ticketmultab_pkey" PRIMARY KEY ("tcode")
 
);

#################### QUERIES ############################

/* These queries are called maximum 4 times with different  matching strings */

/* QUERY No 1  ---------- */

select count(tickettab.tokenid) as ticketcount
from tickettab,subscribertab
where (tickettab.arrivaldate >='2001-12-12'
and tickettab.arrivaldate <='2002-01-12') and
tickettab.subcode=subscribertab.custcode and
((subscribertab.address1 ILIKE '%Cunningham%') OR
(subscribertab.address2 ILIKE '%Cunningham%'))

/* QUERY No 2  ---------- */

select count(ticketmultab.tokenid) as ticketmulcount
from ticketmultab,subscribertab
where (ticketmultab.arrivaldate >='2001-12-12'
and ticketmultab.arrivaldate <='2002-01-12') and
(substring(tokenid FROM 0 FOR strpos(tokenid,'A')))=subscribertab.custcode and
((subscribertab.address1 ILIKE '%Cunningham%') OR
(subscribertab.address2 ILIKE '%Cunningham%'))


######################EXPLAINS ON QUERIES ########################

/* EXPLAIN ON QUERY No 1  --------------------- */

NOTICE:  QUERY PLAN:

Aggregate  (cost=276.27..276.27 rows=1 width=28) ->  Nested Loop  (cost=0.00..276.26 rows=1 width=28)       ->  Seq
Scanon subscribertab  (cost=0.00..265.87 rows=1 width=8)       ->  Seq Scan on tickettab  (cost=0.00..8.40 rows=160
width=20) 
 
/* EXPLAIN ON QUERY No 1  --------------------- */

NOTICE:  QUERY PLAN:

Aggregate  (cost=269.02..269.02 rows=1 width=20) ->  Nested Loop  (cost=0.00..269.02 rows=1 width=20)       ->  Seq
Scanon subscribertab  (cost=0.00..265.87 rows=1 width=8)       ->  Seq Scan on ticketmultab  (cost=0.00..2.50 rows=33
width=12)


################ END OF REQUIRED DATA ########################

Thanks in advance I hope I have provided the required information.
Basically, I need to optimize my operations, but changing the table
structures may be too late in the day.

-- 
Best regards,Gurudutt                            mailto:guru@indvalley.com

Life is not fair - get used to it.
Bill Gates


Thursday, January 10, 2002, 9:20:36 PM, you wrote:


JE> You've shown that the query takes up a lot of CPU, and that it is
JE> slower than you would like.  We have an idea as to how big the table
JE> is (not very), and we know that you have done your homework and have
JE> vacuum analyzed.

JE> Next we need the query in question (so we can check for some of the
JE> more well known performance pitfalls like using IN on large result
JE> sets), and also the explain output (so we can see what PostgreSQL
JE> thinks of your query).  You also might want to consider including the
JE> schema of the tables involved.

JE> Jason

JE> Gurudutt <guru@indvalley.com> writes:

>> Hi,
>> 
>> I have a pentium III server, running on RHL 7.1 with 256 MB RAM,
>> 
>> The following is output of the "top" command for query which involves
>> fetch from a table with about MAX of 10,000 rows.
>> 
>> -------------------------------------TOP------------------------------
>>   PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
>> 
>>  3174 postgres  19   0  3328 3328  2672 R    99.0  1.3   0:58 postmaster
>>  1199 nobody     9   0  3728 3728  2704 S     0.5  1.4   0:03 httpd
>>  3035 root      10   0  1048 1048   840 R     0.3  0.4   0:15 top
>>     1 root       8   0   544  544   472 S     0.0  0.2   0:04 init
>>     2 root       9   0     0    0     0 SW    0.0  0.0   0:00 keventd
>>     3 root
>> 
>> 
>> Now, my question is, it takes ages(2 mints) for the query to run (regularly
>> VACUUM ANALYZED Database) and if you look closely at the resources
>> consumed by the postgres, it is almost taking away 100% CPU time.
>> 
>> How can we make it faster and to consume less resources ??
>> 
>> Can anybody suggest the steps they are taking for time-critical
>> applications to run efficiently.
>> 
>> Thanks in advance
>> 
>> -- 
>> Best regards,
>>  Gurudutt                          mailto:guru@indvalley.com
>> 
>> Life is not fair - get used to it.
>> Bill Gates
>> 
>> 
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

JE> ---------------------------(end of broadcast)---------------------------
JE> TIP 2: you can get off all lists at once with the unregister command
JE>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)