pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
make query faster??
4+ messages / 2 participants
[nested] [flat]

* make query faster??
@ 2001-12-04 20:58  DI Hasenöhrl <i.hasenoehrl@aon.at>
  0 siblings, 1 reply; 4+ messages in thread

From: DI Hasenöhrl @ 2001-12-04 20:58 UTC (permalink / raw)
  To: pgsql-sql

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=341.63..341.63* mean? I couldn't find an explanation 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=341.63..341.63 rows=3809 width=96)
    ->  Seq Scan on artikel  (cost=0.00..115.09 rows=3809 width=96)
  EXPLAIN

  WAWI=# 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=0.00..115.09 rows=3809 width=96)

  EXPLAIN
  WAWI=#    

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????????   

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: make query faster??
@ 2001-12-04 21:26  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: DI Hasenöhrl <i.hasenoehrl@aon.at>
  0 siblings, 1 reply; 4+ messages in thread

From: Stephan Szabo @ 2001-12-04 21:26 UTC (permalink / raw)
  To: DI Hasenöhrl <i.hasenoehrl@aon.at>; +Cc: pgsql-sql


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.




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: make query faster??
@ 2001-12-05 08:52  DI Hasenöhrl <i.hasenoehrl@aon.at>
  parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 4+ messages in thread

From: DI Hasenöhrl @ 2001-12-05 08:52 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql

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=341.63..341.63* means

Thank you for your advices
-Irina
  ----- Original Message ----- 
  From: Stephan Szabo 
  To: DI Hasenöhrl 
  Cc: pgsql-sql@postgresql.org 
  Sent: Tuesday, December 04, 2001 10:26 PM
  Subject: Re: [SQL] make query faster??



  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.

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: make query faster??
@ 2001-12-05 18:28  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: DI Hasenöhrl <i.hasenoehrl@aon.at>
  0 siblings, 0 replies; 4+ messages in thread

From: Stephan Szabo @ 2001-12-05 18:28 UTC (permalink / raw)
  To: DI Hasenöhrl <i.hasenoehrl@aon.at>; +Cc: pgsql-sql


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.





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2001-12-05 18:28 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-04 20:58 make query faster?? DI Hasenöhrl <i.hasenoehrl@aon.at>
2001-12-04 21:26 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-05 08:52   ` DI Hasenöhrl <i.hasenoehrl@aon.at>
2001-12-05 18:28     ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox