pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Curson prbolem
2+ messages / 2 participants
[nested] [flat]

* Curson prbolem
@ 2006-06-20 12:39  biuro@globeinphotos.com
  0 siblings, 1 reply; 2+ messages in thread

From: biuro@globeinphotos.com @ 2006-06-20 12:39 UTC (permalink / raw)
  To: pgsql-performance

Hi
I have following table:
CREATE TABLE  alias (
       alias_id           BIGSERIAL PRIMARY KEY,
       mask                      VARCHAR(20) NOT NULL DEFAULT '',
);

with index:
CREATE INDEX alias_mask_ind ON alias(mask);


and this table has about 1 million rows.


In DB procedure I execute:
    LOOP
       <........>
        OPEN cursor1 FOR SELECT * FROM alias WHERE mask>=alias_out
ORDER BY mask;
        i:=0;
        LOOP
           i:=i+1;
             FETCH cursor1 INTO alias_row;
           EXIT WHEN i=10;
        END LOOP;
      CLOSE cursor1;
     EXIT WHEN end_number=10000;
    END LOOP;


Such construction is very slow (20 sec. per one iteration) but when I modify SQL
to:
        OPEN cursor1 FOR SELECT * FROM alias WHERE mask>=alias_out
ORDER BY mask LIMIT 100;


it works very fast(whole program executes in 4-7s). It is strange for me becuase
I've understood so far
that when cursor is open select is executed but Postgres does not
select all rows - only cursor is positioned on first row, when you
execute fetch next row is read. But this example shows something
different.


Can somebody clarify what is wrong with my example? I need select
without LIMIT 100 part.


Regards
Michal Szymanski
http://blog.szymanskich.net






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

* Re: Curson prbolem
@ 2006-06-20 14:28  Tom Lane <tgl@sss.pgh.pa.us>
  parent: biuro@globeinphotos.com
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2006-06-20 14:28 UTC (permalink / raw)
  To: biuro@globeinphotos.com; +Cc: pgsql-performance

biuro@globeinphotos.com writes:
> [slow:]
>         OPEN cursor1 FOR SELECT * FROM alias WHERE mask>=alias_out
> ORDER BY mask;
> [fast:]
>         OPEN cursor1 FOR SELECT * FROM alias WHERE mask>=alias_out
> ORDER BY mask LIMIT 100;

The difference is that in the first case the planner has to assume you
intend to fetch all the rows with mask>=something (and I'll bet the
something is a plpgsql variable, so the planner can't even see its
value).  In this case a sort-based plan looks like a winner.  In the
second case, since you only need to fetch 100 rows, it's clearly best to
scan the index beginning at mask = alias_out.

> Can somebody clarify what is wrong with my example? I need select
> without LIMIT 100 part.

Why?  You should always tell the SQL engine what it is that you really
want --- leaving it in the dark about your intentions is a good way to
destroy performance, as you are finding out.  If I were you I would get
rid of the row-counting inside the loop entirely, and use the "LIMIT n"
clause to handle that.

			regards, tom lane



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

end of thread, other threads:[~2006-06-20 14:28 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-06-20 12:39 Curson prbolem biuro@globeinphotos.com
2006-06-20 14:28 ` Tom Lane <tgl@sss.pgh.pa.us>

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