From i.hasenoehrl@aon.at Tue Dec 4 20:59:39 2001 Received: from email03.aon.at (WARSL401PIP7.highway.telekom.at [195.3.96.115]) by postgresql.org (8.11.3/8.11.4) with SMTP id fB4Kxcm16394 for ; Tue, 4 Dec 2001 15:59:39 -0500 (EST) (envelope-from i.hasenoehrl@aon.at) Received: (qmail 613626 invoked from network); 4 Dec 2001 20:59:31 -0000 Received: from n649p026.adsl.highway.telekom.at (HELO irina) ([62.47.25.26]) (envelope-sender ) by qmail3.highway.telekom.at (qmail-ldap-1.03) with SMTP for ; 4 Dec 2001 20:59:31 -0000 Message-ID: <004a01c17d06$5f61b840$01011eac@irina> From: =?iso-8859-1?Q?DI_Hasen=F6hrl?= To: Subject: make query faster?? Date: Tue, 4 Dec 2001 21:58:06 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0047_01C17D0E.C0FCED60" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200112/38 X-Sequence-Number: 5861 This is a multi-part message in MIME format. ------=_NextPart_000_0047_01C17D0E.C0FCED60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I've a simple query, but when I use *ORDER BY* it takes a long time till I = can see the result. a_nr is varchar(20) and the primary key of my table artikel. Please, can someone give me a hint, how to improve my query. Many thanks in advance -Irina P.S.: what does *cost=3D341.63..341.63* mean? I couldn't find an explanatio= n in the archives! WAWI-# select a_nr,a_bez1,a_bez2,a_bez3,a_bez4,a_gewicht,a_bruttog,kl_ean= ,a_vol from artikel order by a_nr; NOTICE: QUERY PLAN: Sort (cost=3D341.63..341.63 rows=3D3809 width=3D96) -> Seq Scan on artikel (cost=3D0.00..115.09 rows=3D3809 width=3D96) EXPLAIN WAWI=3D# explain WAWI-# select a_nr,a_bez1,a_bez2,a_bez3,a_bez4,a_gewicht,a_bruttog,kl_ean= ,a_vol from artikel; NOTICE: QUERY PLAN: Seq Scan on artikel (cost=3D0.00..115.09 rows=3D3809 width=3D96) EXPLAIN WAWI=3D#=20=20=20=20 In Bruce Momjian's book I read, that indexes are usually not used for ORDER= BY operations: a sequential scan followed by an explicit sort is fatser th= an an indexscan. What's an explicit sort????????=20=20=20 ------=_NextPart_000_0047_01C17D0E.C0FCED60 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
I've a simple query, but when I use *ORDER= BY* it=20 takes a long time till I can see the result.
 
a_nr is varchar(20) and the primary key of= my table=20 artikel.
 
Please, can someone give me a hint, how to= improve=20 my query.
Many thanks in advance
 
-Irina
 
P.S.: what does *cost=3D341.63..341.63* me= an? I=20 couldn't find an explanation in the archives!
 
WAWI-# select=20 a_nr,a_bez1,a_bez2,a_bez3,a_bez4,a_gewicht,a_bruttog,kl_ean,a_vol fr= om=20 artikel order by a_nr;
NOTICE:  QUERY=20 PLAN:
 
Sort  (cost=3D341.63..341.6= 3=20 rows=3D3809 width=3D96)
  ->  Seq Scan on artike= l =20 (cost=3D0.00..115.09 rows=3D3809 width=3D96)
EXPLAIN

WAWI=3D# explain
WAWI-# select=20 a_nr,a_bez1,a_bez2,a_bez3,a_bez4,a_gewicht,a_bruttog,kl_ean,a_vol fr= om=20 artikel;
NOTICE:  QUERY PLAN:
 
Seq Scan on artikel  (cost=3D0.00..= 115.09=20 rows=3D3809 width=3D96)
 
EXPLAIN
WAWI=3D#    
 
In Bruce Momjian's book I r= ead, that=20 indexes are usually not used for ORDER BY operations: a sequential scan fol= lowed=20 by an explicit sort is fatser than an indexscan.
 
What's an explicit=20 sort????????  
------=_NextPart_000_0047_01C17D0E.C0FCED60-- From sszabo@megazone23.bigpanda.com Tue Dec 4 21:26:38 2001 Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fB4LQcm17580 for ; Tue, 4 Dec 2001 16:26:38 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id A4B0ED5E6; Tue, 4 Dec 2001 13:26:39 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 9A6315BEA; Tue, 4 Dec 2001 13:26:39 -0800 (PST) Date: Tue, 4 Dec 2001 13:26:39 -0800 (PST) From: Stephan Szabo To: =?iso-8859-1?Q?DI_Hasen=F6hrl?= Cc: Subject: Re: make query faster?? In-Reply-To: <004a01c17d06$5f61b840$01011eac@irina> Message-ID: <20011204131843.I6514-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by postgresql.org id fB4LQqL17590 X-Archive-Number: 200112/39 X-Sequence-Number: 5862 On Tue, 4 Dec 2001, [iso-8859-1] DI Hasenöhrl wrote: > I've a simple query, but when I use *ORDER BY* it takes a long time > till I can see the result. > > a_nr is varchar(20) and the primary key of my table artikel. > > Please, can someone give me a hint, how to improve my query. If you have not yet done so, you may want to increase sort_mem in your postgresql.conf assuming you have sufficient memory in your machine (and shared_buffers while you're at it). The defaults are fairly low and you may see a performance gain in general if you raise them to amounts more reasonable for your machine. > In Bruce Momjian's book I read, that indexes are usually not used for > ORDER BY operations: a sequential scan followed by an explicit sort is > fatser than an indexscan. > > What's an explicit sort???????? Actually sorting the results of the sequence scan rather than using the fact that the index keys can be gotten in sorted order to get the rows in order. From i.hasenoehrl@aon.at Wed Dec 5 08:54:18 2001 Received: from email03.aon.at (WARSL401PIP7.highway.telekom.at [195.3.96.115]) by postgresql.org (8.11.3/8.11.4) with SMTP id fB58sHm49286 for ; Wed, 5 Dec 2001 03:54:18 -0500 (EST) (envelope-from i.hasenoehrl@aon.at) Received: (qmail 225402 invoked from network); 5 Dec 2001 08:54:17 -0000 Received: from n649p026.adsl.highway.telekom.at (HELO irina) ([62.47.25.26]) (envelope-sender ) by qmail3.highway.telekom.at (qmail-ldap-1.03) with SMTP for ; 5 Dec 2001 08:54:17 -0000 Message-ID: <003601c17d6a$398f9100$01011eac@irina> From: =?iso-8859-1?Q?DI_Hasen=F6hrl?= To: "Stephan Szabo" Cc: References: <20011204131843.I6514-100000@megazone23.bigpanda.com> Subject: Re: make query faster?? Date: Wed, 5 Dec 2001 09:52:52 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0033_01C17D72.9B1876A0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200112/43 X-Sequence-Number: 5866 This is a multi-part message in MIME format. ------=_NextPart_000_0033_01C17D72.9B1876A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thank you for your response, but I'm not quite clear. Is *sort_mem* the same as postgres' starting option -S *Sortsize*, which is= by default 512Kb At this time I increased sortsize to 1024Kb, but the costs from explain are= the same as before and I can't see, that the query is faster Please, can you explain, what *cost=3D341.63..341.63* means Thank you for your advices -Irina ----- Original Message -----=20 From: Stephan Szabo=20 To: DI Hasen=F6hrl=20 Cc: pgsql-sql@postgresql.org=20 Sent: Tuesday, December 04, 2001 10:26 PM Subject: Re: [SQL] make query faster?? On Tue, 4 Dec 2001, [iso-8859-1] DI Hasen=F6hrl wrote: > I've a simple query, but when I use *ORDER BY* it takes a long time > till I can see the result. > > a_nr is varchar(20) and the primary key of my table artikel. > > Please, can someone give me a hint, how to improve my query. If you have not yet done so, you may want to increase sort_mem in your postgresql.conf assuming you have sufficient memory in your machine (and shared_buffers while you're at it). The defaults are fairly low and you may see a performance gain in general if you raise them to amounts more reasonable for your machine. > In Bruce Momjian's book I read, that indexes are usually not used for > ORDER BY operations: a sequential scan followed by an explicit sort is > fatser than an indexscan. > > What's an explicit sort???????? Actually sorting the results of the sequence scan rather than using the fact that the index keys can be gotten in sorted order to get the rows in order. ------=_NextPart_000_0033_01C17D72.9B1876A0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Thank you for your response, but I'm not q= uite=20 clear.
 
Is *sort_mem* the same as postgres' s= tarting=20 option -S *Sortsize*, which is by default 512Kb
 
At this time I increased sortsize to 1024K= b, but=20 the costs from explain are the same as before and I can't see, that the que= ry is=20 faster
 
Please, can you explain, what *cost=3D341.= 63..341.63*=20 means
 
Thank you for your advices
-Irina
----- Original Message -----
Fro= m:=20 Stephan Szabo
Cc: pgsql-sql@postgresql.org
Sent: Tuesday, December 04, 2001 1= 0:26=20 PM
Subject: Re: [SQL] make query=20 faster??


On Tue, 4 Dec 2001, [iso-8859-1] DI Hasen=F6hrl=20 wrote:

> I've a simple query, but when I use *ORDER BY* it take= s a=20 long time
> till I can see the result.
>
> a_nr is=20 varchar(20) and the primary key of my table artikel.
>
> Plea= se,=20 can someone give me a hint, how to improve my query.

If you have n= ot=20 yet done so, you may want to increase sort_mem in your
postgresql.conf= =20 assuming you have sufficient memory in your machine
(and shared_buffer= s=20 while you're at it). The defaults are fairly low and
you may see a=20 performance gain in general if you raise them to amounts
more reasonab= le=20 for your machine.

> In Bruce Momjian's book I read, that indexe= s are=20 usually not used for
> ORDER BY operations: a sequential scan follo= wed=20 by an explicit sort is
> fatser than an indexscan.
>
>= =20 What's an explicit sort????????

Actually sorting the results of th= e=20 sequence scan rather than using the
fact that the index keys can be go= tten=20 in sorted order to get the rows
in order.

<= /HTML> ------=_NextPart_000_0033_01C17D72.9B1876A0-- From sszabo@megazone23.bigpanda.com Wed Dec 5 18:28:54 2001 Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fB5ISsm88885 for ; Wed, 5 Dec 2001 13:28:54 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 83C17D5E9; Wed, 5 Dec 2001 10:28:54 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 7969E5BEA; Wed, 5 Dec 2001 10:28:54 -0800 (PST) Date: Wed, 5 Dec 2001 10:28:54 -0800 (PST) From: Stephan Szabo To: =?iso-8859-1?Q?DI_Hasen=F6hrl?= Cc: Subject: Re: make query faster?? In-Reply-To: <003601c17d6a$398f9100$01011eac@irina> Message-ID: <20011205101538.S16316-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by postgresql.org id fB5IaAn89242 X-Archive-Number: 200112/53 X-Sequence-Number: 5876 On Wed, 5 Dec 2001, [iso-8859-1] DI Hasenöhrl wrote: > Thank you for your response, but I'm not quite clear. > > Is *sort_mem* the same as postgres' starting option -S *Sortsize*, > which is by default 512Kb > At this time I increased sortsize to 1024Kb, but the costs from > explain are the same as before and I can't see, that the query is > faster IIRC the explain costs may not change. If your table really only has 4000 rows, I don't see why it would need to drop off to disk, but while the query is running, you can see if pg_sorttemp* files are being created under your data directory (which would show that it is going out to disk and that you may need to raise -S again). Otherwise, is the data involved something you can share? It might help for us to be able to try it elsewhere to see how it performs. > Please, can you explain, what *cost=341.63..341.63* means It's an estimate of cost. I believe the first is startup cost and the last total cost. You probably want to read the performance tips section of the user's guide.