Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 0BB0F9FBE17 for ; Wed, 20 Jun 2007 12:56:05 -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 82993-06 for ; Wed, 20 Jun 2007 12:55:57 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.227]) by postgresql.org (Postfix) with ESMTP id BB3B89FBE12 for ; Wed, 20 Jun 2007 12:55:57 -0300 (ADT) Received: by wx-out-0506.google.com with SMTP id s16so201573wxc for ; Wed, 20 Jun 2007 08:55:56 -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=AfQ+RncL+mXK5EI1qpN5zTv+ogOqbwcyxgybvnyIca3fPBiuQvHVb6PZ7MQsgDeH9ZNvYQflSm9jnBibEnTjYE/nNiZZ42ouBXscb26AyaeGbg77BraWhzwKvT/nvpdfHa4R3Rongah3opPNWVL/QqrGh7wx8uc3c9Iwumr6Aos= 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=XrkSFKesKf1T35m6V8/w9sjER5Xn3JECI4Woa8yoDrYGx2VeuPTqPuCqD+I0erL0RVsJIB6DUoonUR+lL58stO5iJofIxIaPGdL88+RQztPtRdt6II9yahIccKfz2olv5X+j7UaU6Fyckkq+/Wv7brWMZPtMR1BSh54+823E03k= Received: by 10.70.11.1 with SMTP id 1mr1271995wxk.1182354956823; Wed, 20 Jun 2007 08:55:56 -0700 (PDT) Received: by 10.70.13.13 with HTTP; Wed, 20 Jun 2007 08:55:56 -0700 (PDT) Message-ID: <2ca799770706200855w1a6b6a7cy8526b9be0ef9c97c@mail.gmail.com> Date: Wed, 20 Jun 2007 18:55:56 +0300 From: "Mikko Partio" To: "Tom Lane" Subject: Re: Slow indexscan Cc: pgsql-performance@postgresql.org In-Reply-To: <17907.1182352197@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_99997_13875237.1182354956784" References: <2ca799770706200702y1074f066s61279b67f93315d0@mail.gmail.com> <17907.1182352197@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200706/422 X-Sequence-Number: 25455 ------=_Part_99997_13875237.1182354956784 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: > > > Index Scan using tbl_20070601_pkey on tbl_20070601 t1 > > (cost=0.00..365.13rows=13 width=137) (actual time= > > 120.83..10752.64 rows=539 loops=1) > > Index Cond: ((validtime >= 20070602000000::bigint) AND (validtime <= > > 20070602235500::bigint) AND (latitude = 60.2744::double precision) AND > > (longitude = 26.4417::double precision)) > > Filter: (parname = 'temperature'::character varying) > > You do realize that's going to scan the entire index range from > 20070602000000 to 20070602235500? > > If this is a typical query you'd be better off putting the lat/long > columns first in the index. > > regards, tom lane Thanks for the reply. Adding a new index does not speed up the query (although the planner decides to use the index): db=# create index tbl_20070601_latlonvalidpar_index on tbl_20070601 (latitude,longitude,validtime,parname); CREATE INDEX db=# explain analyze select * 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_index on tbl_20070601 t1 (cost=0.00..29.18 rows=13 width=137) (actual time=3471.94..31542.90 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: 31544.48 msec (3 rows) This is a very typical query and therefore it should be made as fast as possible. There are several tables like this rowcount ranging from 3 million to 13 million. I have some possibilities to modify the queries as well as the tables, but the actual table structure is hard coded. Any other suggestions? Regards MP ------=_Part_99997_13875237.1182354956784 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

On 6/20/07, Tom Lane <tgl@sss.pgh.p= a.us> wrote:
"Mikko Partio" <mpartio@g= mail.com> writes:

>  Index Scan using tbl_200706= 01_pkey on tbl_20070601 t1
> (cost=3D0.00..365.13rows=3D13 width=3D13= 7) (actual time=3D
> 120.83..10752.64 rows=3D539 loops=3D1)
>   &n= bsp;Index Cond: ((validtime >=3D 20070602000000::bigint) AND (validtime = <=3D
> 20070602235500::bigint) AND (latitude =3D 60.2744::double p= recision) AND
> (longitude =3D=20 26.4417::double precision))
>    Filter: (parname= =3D 'temperature'::character varying)

You do realize that&#= 39;s going to scan the entire index range from
20070602000000 to 2007060= 2235500?

If this is a typical query you'd be better off putting = the lat/long
columns first in the index.

      =             &nb= sp;     regards, tom lane

Thanks for the reply.

Adding a new index does not speed up the quer= y (although the planner decides to use the index):

db=3D# create index tbl_20070601_latlonvalidpar_index on tbl_200706= 01 (latitude,longitude,validtime,parname);
CREATE INDEX

db=3D# ex= plain analyze select * from tbl_20070601 where validtime between 2007060200= 0000 and 20070602235500 and latitude=3D 60.2744 and longitude=3D26.4417 and parname in ('temperature');
=             &nb= sp;            =             &nb= sp;            =             &nb= sp;            =             &nb= sp;            =            QUERY PLAN&nbs= p;         
--------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------
 Index Scan using tbl_20070601_latlonvalidpar_index on tbl_2007060= 1 t1  (cost=3D0.00..29.18 rows=3D13 width=3D137) (actual time=3D3471.9= 4..31542.90 rows=3D539 loops=3D1)
   Index Cond: ((latitude = =3D 60.2744::double precision) AND (longitude =3D=20 26.4417::double precision) AND (validtime >=3D 20070602000000::bigint) A= ND (validtime <=3D 20070602235500::bigint) AND (parname =3D 'tempera= ture'::character varying))
 Total runtime: 31544.48 msec
(3 = rows)


This is a very typical query and therefore it should be made as fas= t as possible. There are several tables like this rowcount ranging from 3 m= illion to 13 million. I have some possibilities to modify the queries as we= ll as the tables, but the actual table structure is hard coded.=20

Any other suggestions?

Regards

MP

=
------=_Part_99997_13875237.1182354956784--