Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id F11C49FB4F5 for ; Wed, 20 Jun 2007 13:43:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 09249-04 for ; Wed, 20 Jun 2007 13:43:33 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.231]) by postgresql.org (Postfix) with ESMTP id 4C7809FB33D for ; Wed, 20 Jun 2007 13:43:34 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id s16so214976wxc for ; Wed, 20 Jun 2007 09:43:33 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=CdxUUaLuFlbrSn2CBO9TaSHltpU5tlB1cgJokheOGqcV3SqDxzrJ+aHuBixln1+MiQ9a4FKNtZivL4xNE3kK8Z2pABsO2pTXyG6WWVNDJ5zaZlP6cQ+R/oevfexppNIyED+imTLnYRxgKMxBVgpSutVMJCV9mqiEAn5f4I9fw4I= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=hhytT8olx9HDJf3sGN4DkQ2S0IxZRKTZklTtnA2daOINHibvv/qfeam7fTWIWv8sL7UU96h3dhl0AVUInFuD6l8T8PfiWE7i72HNwSxsP4eW4RPQaIim0f/jJUqC/3yBSiJTwnfZS1bLId3rwAueF08YPhEgu11oREqq+3f0am0= Received: by 10.70.74.6 with SMTP id w6mr1405946wxa.1182357813369; Wed, 20 Jun 2007 09:43:33 -0700 (PDT) Received: by 10.70.13.13 with HTTP; Wed, 20 Jun 2007 09:43:33 -0700 (PDT) Message-ID: <2ca799770706200943l6ae9f6bfif54683e21fa33f35@mail.gmail.com> Date: Wed, 20 Jun 2007 19:43:33 +0300 From: "Mikko Partio" To: "Tom Lane" Subject: Re: Slow indexscan Cc: pgsql-performance@postgresql.org In-Reply-To: <18952.1182355276@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_100530_30755244.1182357813327" References: <2ca799770706200702y1074f066s61279b67f93315d0@mail.gmail.com> <17907.1182352197@sss.pgh.pa.us> <2ca799770706200855w1a6b6a7cy8526b9be0ef9c97c@mail.gmail.com> <18952.1182355276@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/426 X-Sequence-Number: 25459 ------=_Part_100530_30755244.1182357813327 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 6/20/07, Tom Lane wrote: > > "Mikko Partio" writes: > > Adding a new index does not speed up the query (although the planner > decides > > to use the index): > > Hm. Lots of dead rows maybe? What's your vacuuming policy? > > regards, tom lane The table only gets inserts and selects, never updates or deletes so I guess vacuuming isn't necessary. Anyways: db=# SET default_statistics_target TO 1000; SET db=# vacuum analyze verbose tbl_20070601; INFO: --Relation public.tbl_20070601-- INFO: Index tbl_20070601_pkey: Pages 95012; Tuples 3715565: Deleted 0. CPU 8.63s/1.82u sec elapsed 367.57 sec. INFO: Index tbl_20070601_latlonvalidpar_index: Pages 27385; Tuples 3715565: Deleted 0. CPU 1.55s/1.22u sec elapsed 23.27 sec. INFO: Removed 2865 tuples in 2803 pages. CPU 0.30s/0.20u sec elapsed 37.91 sec. INFO: Pages 83950: Changed 0, Empty 0; Tup 3715565: Vac 2865, Keep 0, UnUsed 0. Total CPU 12.32s/3.69u sec elapsed 449.98 sec. INFO: Analyzing public.tbl_20070601 VACUUM db=# set sort_mem to 50000; SET db=# explain analyze * from tbl_20070601 where validtime between 20070602000000 and 20070602235500 and latitude=60.2744 and longitude=26.4417and parname in ('temperature'); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using tbl_20070601_latlonvalidpar on tbl_20070601 t1 (cost= 0.00..28.46 rows=13 width=137) (actual time=37.81..1415.06 rows=539 loops=1) Index Cond: ((latitude = 60.2744::double precision) AND (longitude = 26.4417::double precision) AND (validtime >= 20070602000000::bigint) AND (validtime <= 20070602235500::bigint) AND (parname = 'temperature'::character varying)) Total runtime: 1416.53 msec (3 rows) I guess the sort_mem helped, or then part of the rows are in the cache already. Should increasing sort_mem help here since there are no sorts etc? Regards MP ------=_Part_100530_30755244.1182357813327 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On 6/20/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Mikko Partio" <mpartio@gmail.com> writes:
> Adding a new index does not speed up the query (although the planner decides
> to use the index):

Hm.  Lots of dead rows maybe?  What's your vacuuming policy?

                        regards, tom lane



The table only gets inserts and selects, never updates or deletes so I guess vacuuming isn't necessary. Anyways:


db=# SET default_statistics_target TO 1000;
SET
db=# vacuum analyze verbose tbl_20070601;
INFO:  --Relation public.tbl_20070601--
INFO:  Index tbl_20070601_pkey: Pages 95012; Tuples 3715565: Deleted 0.
        CPU 8.63s/1.82u sec elapsed 367.57 sec.
INFO:  Index tbl_20070601_latlonvalidpar_index: Pages 27385; Tuples 3715565: Deleted 0.
        CPU 1.55s/1.22u sec elapsed 23.27 sec.
INFO:  Removed 2865 tuples in 2803 pages.
        CPU 0.30s/0.20u sec elapsed 37.91 sec.
INFO:  Pages 83950: Changed 0, Empty 0; Tup 3715565: Vac 2865, Keep 0, UnUsed 0.
        Total CPU 12.32s/3.69u sec elapsed 449.98 sec.
INFO:  Analyzing public.tbl_20070601
VACUUM
db=# set sort_mem to 50000;
SET
db=# explain analyze * from tbl_20070601 where validtime between 20070602000000 and 20070602235500 and latitude=60.2744 and longitude=26.4417 and parname in ('temperature');
                                                                                                               QUERY PLAN          
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tbl_20070601_latlonvalidpar on tbl_20070601 t1  (cost=0.00..28.46 rows=13 width=137) (actual time=37.81..1415.06 rows=539 loops=1)
   Index Cond: ((latitude = 60.2744::double precision) AND (longitude = 26.4417::double precision) AND (validtime >= 20070602000000::bigint) AND (validtime <= 20070602235500::bigint) AND (parname = 'temperature'::character varying))
 Total runtime: 1416.53 msec
(3 rows)


I guess the sort_mem helped, or then part of the rows are in the cache already. Should increasing sort_mem help here since there are no sorts etc?

Regards

MP


------=_Part_100530_30755244.1182357813327--