From: Tom Lane <tgl@sss.pgh.pa.us>
To: biuro@globeinphotos.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Curson prbolem
Date: Tue, 20 Jun 2006 10:28:18 -0400
Message-ID: <27874.1150813698@sss.pgh.pa.us> (raw)
In-Reply-To: <20060620143932.e7u8gr5s53i808ss@gdn.superhost.pl>
References: <20060620143932.e7u8gr5s53i808ss@gdn.superhost.pl>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, biuro@globeinphotos.com
Subject: Re: Curson prbolem
In-Reply-To: <27874.1150813698@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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