X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id D63C1D1B4F6 for ; Tue, 9 Sep 2003 06:28:38 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 35837-04 for ; Tue, 9 Sep 2003 03:28:09 -0300 (ADT) Received: from master.nslk.com (master.nslk.com [66.78.39.110]) by svr1.postgresql.org (Postfix) with ESMTP id E3A89D1B4E9 for ; Tue, 9 Sep 2003 03:27:50 -0300 (ADT) Received: from [203.94.84.129] (helo=waruna) by master.nslk.com with asmtp (Exim 4.20) id 19wbye-0001eN-GG for pgsql-performance@postgresql.org; Tue, 09 Sep 2003 02:27:49 -0400 Message-ID: <003201c3769b$75e7f490$81545ecb@nirmani> Reply-To: "Waruna Geekiyanage" From: "Waruna Geekiyanage" To: Subject: Slow query? Date: Tue, 9 Sep 2003 12:27:27 +0600 Organization: Nirmani Solutions MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002E_01C376CD.BAF6DE20" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2727.1300 X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - master.nslk.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - nirmani.com X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=4.0 tagged_above=0.0 required=5.0 tests=BAYES_30, HTML_30_40, RCVD_IN_DSBL X-Spam-Level: **** X-Archive-Number: 200309/142 X-Sequence-Number: 3508 This is a multi-part message in MIME format. ------=_NextPart_000_002E_01C376CD.BAF6DE20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi All, Is it usual that the following query to take 22 secs with the machine I ha= ve? Any other reason? Hope I have provided all the details need. Thanks, Waruna Tables: /* --------------------------------------------------------=20 Table structure for table "tvDiary"=20 -------------------------------------------------------- */ CREATE TABLE "tvDiary" ( "member" int4 NOT NULL, "timeSlot" int2 NOT NULL references "timeSlot"("code"), "channel" varchar(4) NOT NULL references "tvChannel"("code"), "date" date NOT NULL, CONSTRAINT "tvDiary_pkey" PRIMARY KEY ("date", "member", "timeSlot") ); Indexed on "date" /* --------------------------------------------------------=20 Table structure for table "mDiary"=20 -------------------------------------------------------- */ CREATE TABLE "mDiary" ( "member" int4 NOT NULL, "area" char(1) NOT NULL, "district" int2 references "district"("code"), "date" date NOT NULL, CONSTRAINT "mDiary_pkey" PRIMARY KEY ("date", "member") ); Indexed on "date" # Records tvDiary : 7 300 000 mDiary : 850 000 machine :=20 Celeron 1.0GHz RAM - 390MB , 40 GB IDE HDD RedHat Linux 9 kernel.shmmni =3D 4096 kernel.shmall =3D 33554432 kernel.shmmax =3D 134217728 postgres 7.3.4 shared_buffers =3D 8192 sort_mem =3D 65536 Query: SELECT COUNT(td.member) AS count, td.date AS date, td."timeSlot" AS "timeSl= ot", td.channel AS channel,=20=20=20 tg.district AS district,tg.area AS area=20 FROM "tvDiary" td ,(SELECT DISTINCT(md.member) AS member, md.area AS area, = md.district as district=20 FROM "mDiary" md=20 WHERE (md.date BETWEEN '20020301' AND '2002033= 0') ) AS tg=20 WHERE(td.date BETWEEN '20020301' AND '20020330') AND (td.member=3Dtg.member= )=20 GROUP BY td.date,td."timeSlot", td.channel,tg.district,tg.area; QUERY PLAN ---------------------------------------------------------------------------= ----- ---------------------------------------------------------------------------= ----- ----------------------- Aggregate (cost=3D91790.44..100942.65 rows=3D52298 width=3D28) (actual ti= me=3D18396.42..21764.44 rows=3D57478 loops=3D1) -> Group (cost=3D91790.44..99635.19 rows=3D522983 width=3D28) (actual = time=3D18396.34..21158.23 rows=3D281733 loops=3D1) -> Sort (cost=3D91790.44..93097.90 rows=3D522983 width=3D28) (ac= tual time=3D18396.30..18588.91 rows=3D281733 loops=3D1) Sort Key: td.date, td."timeSlot", td.channel, tg.district, t= g.area -> Merge Join (cost=3D34290.10..42116.42 rows=3D522983 wid= th=3D28) (actual time=3D8159.30..10513.62 rows=3D281733 ops=3D1) Merge Cond: ("outer".member =3D "inner".member) -> Sort (cost=3D29121.48..29755.35 rows=3D253551 wid= th=3D17) (actual time=3D6752.36..6933.38 rows=3D282552 loops=3D1) Sort Key: td.member -> Index Scan using d_tvdiary_key on "tvDiary" = td (cost=3D0.00..6362.82 rows=3D253551 width=3D17) (actual time=3D95.80..4= 766.25 rows=3D282587 loops=3D1) Index Cond: ((date >=3D '2002-03-01'::date= ) AND (date <=3D '2002-03-30'::date)) -> Sort (cost=3D5168.63..5179.26 rows=3D4251 width= =3D11) (actual time=3D1406.88..1590.72 rows=3D281955 loops=3D1) Sort Key: tg.member -> Subquery Scan tg (cost=3D4487.31..4912.42 r= ows=3D4251 width=3D11) (actual time=3D1228.55..1397.20 rows=3D2348 loops=3D= 1) -> Unique (cost=3D4487.31..4912.42 rows= =3D4251 width=3D11) (actual time=3D1228.52..1390.12 rows=3D2348 loops=3D1) -> Sort (cost=3D4487.31..4593.59 r= ows=3D42511 width=3D11) (actual time=3D1228.51..1257.87 rows=3D46206 loops= =3D1) Sort Key: member, area, distri= ct -> Index Scan using d_mdiary_= key on "mDiary" md (cost=3D0.00..1219.17 rows=3D42511 width=3D11) (actual = time=3D60.20..750. 67 rows=3D46206 loops=3D1) Index Cond: ((date >=3D = '2002-03-01'::date) AND (date <=3D '2002-03-30'::date)) Total runtime: 21992.24 msec (19 rows) ------=_NextPart_000_002E_01C376CD.BAF6DE20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi All,
 Is it usual that the following query= to take=20 22 secs with the machine I have?
Any other reason?
Hope I have provided all the details= =20 need.
 
Thanks,
Waruna
 
Tables:
/*=20 --------------------------------------------------------
  Table= =20 structure for table "tvDiary"=20
-------------------------------------------------------- */
CREATE T= ABLE=20 "tvDiary" (
    "member" int4 NOT NULL,
  &n= bsp;=20 "timeSlot" int2 NOT NULL references "timeSlot"("code"),
  =20 "channel" varchar(4) NOT NULL references "tvChannel"("code"),
 &nbs= p;=20 "date" date NOT NULL,
   CONSTRAINT "tvDiary_pkey" PRIMARY KEY= =20 ("date", "member", "timeSlot")
);
Indexed on "date"
 
/*=20 --------------------------------------------------------
  Table= =20 structure for table "mDiary"=20
-------------------------------------------------------- */
CREATE T= ABLE=20 "mDiary" (
   "member" int4 NOT NULL,
   "area"= =20 char(1) NOT NULL,
   "district" int2 references=20 "district"("code"),
   "date" date NOT NULL,
  = =20 CONSTRAINT "mDiary_pkey" PRIMARY KEY ("date", "member")
);
Indexed on= =20 "date"
 
# Records
tvDiary : 7 300 000
mDiary= : 850=20 000
 
machine :
Celeron 1.0GHz RAM - 390MB ,= 40 GB=20 IDE HDD
RedHat Linux 9
 
kernel.shmmni =3D 4096
kernel.shmall = =3D=20 33554432
kernel.shmmax =3D 134217728
 
postgres 7.3.4
 
shared_buffers =3D 8192
sort_mem =3D=20 65536
 
Query:
 
SELECT COUNT(td.member) AS count, td.date = AS date,=20 td."timeSlot" AS "timeSlot", td.channel AS=20 channel,   
    tg.district AS district= ,tg.area=20 AS area
FROM "tvDiary" td ,(SELECT DISTINCT(md.member) AS member, md.ar= ea AS=20 area, md.district as district
       = ;=20                = =20     FROM "mDiary" md
    =20                = =20         WHERE (md.date BETWEEN '20020301' AND= =20 '20020330') ) AS tg
WHERE(td.date BETWEEN '20020301' AND '20020330') AN= D=20 (td.member=3Dtg.member)
GROUP BY td.date,td."timeSlot",=20 td.channel,tg.district,tg.area;
 
 QUERY PLAN
 
------------------------------------------------------------------= --------------
---------------------------------------------------------= -----------------------
-----------------------
 Aggregate = =20 (cost=3D91790.44..100942.65 rows=3D52298 width=3D28) (actual time=3D18396.4= 2..21764.44=20 rows=3D57478 loops=3D1)
   ->  Group =20 (cost=3D91790.44..99635.19 rows=3D522983 width=3D28) (actual time=3D18396.3= 4..21158.23=20 rows=3D281733 loops=3D1)
        = ;=20 ->  Sort  (cost=3D91790.44..93097.90 rows=3D522983 width=3D28)= (actual=20 time=3D18396.30..18588.91 rows=3D281733=20 loops=3D1)
          &= nbsp;   =20 Sort Key: td.date, td."timeSlot", td.channel, tg.district,=20 tg.area
          &nbs= p;   =20 ->  Merge Join  (cost=3D34290.10..42116.42 rows=3D522983 width= =3D28)=20 (actual time=3D8159.30..10513.62 rows=3D281733=20 ops=3D1)
          &nb= sp;         =20 Merge Cond: ("outer".member =3D=20 "inner".member)
         &n= bsp;          =20 ->  Sort  (cost=3D29121.48..29755.35 rows=3D253551 width=3D17)= (actual=20 time=3D6752.36..6933.38 rows=3D282552=20 loops=3D1)
          &= nbsp;           &nbs= p;   =20 Sort Key:=20 td.member
          &n= bsp;            = ;   =20 ->  Index Scan using d_tvdiary_key on "tvDiary" td =20 (cost=3D0.00..6362.82 rows=3D253551 width=3D17) (actual time=3D95.80..4766.= 25=20 rows=3D282587
 loops=3D1)
      &n= bsp;            = ;            &n= bsp;=20 Index Cond: ((date >=3D '2002-03-01'::date) AND (date <=3D=20 '2002-03-30'::date))
        &nb= sp;           =20 ->  Sort  (cost=3D5168.63..5179.26 rows=3D4251 width=3D11) (ac= tual=20 time=3D1406.88..1590.72 rows=3D281955=20 loops=3D1)
          &= nbsp;           &nbs= p;   =20 Sort Key:=20 tg.member
          &n= bsp;            = ;   =20 ->  Subquery Scan tg  (cost=3D4487.31..4912.42 rows=3D4251 wid= th=3D11)=20 (actual time=3D1228.55..1397.20 rows=3D2348=20 loops=3D1)
          &= nbsp;           &nbs= p;         =20 ->  Unique  (cost=3D4487.31..4912.42 rows=3D4251 width=3D11) (= actual=20 time=3D1228.52..1390.12 rows=3D2348=20 loops=3D1)
          &= nbsp;           &nbs= p;            &= nbsp;  =20 ->  Sort  (cost=3D4487.31..4593.59 rows=3D42511 width=3D11) (a= ctual=20 time=3D1228.51..1257.87 rows=3D46206=20 loops=3D1)
          &= nbsp;           &nbs= p;            &= nbsp;        =20 Sort Key: member, area,=20 district
          &nb= sp;            =             &nb= sp;        =20 ->  Index Scan using d_mdiary_key on "mDiary" md =20 (cost=3D0.00..1219.17 rows=3D42511 width=3D11) (actual time=3D60.20..750.67=20 rows=3D46206=20 loops=3D1)
          &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;  =20 Index Cond: ((date >=3D '2002-03-01'::date) AND (date <=3D=20 '2002-03-30'::date))
 Total runtime: 21992.24 msec
(19=20 rows)
------=_NextPart_000_002E_01C376CD.BAF6DE20--