From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | biuro(at)globeinphotos(dot)com |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Curson prbolem |
Date: | 2006-06-20 14:28:18 |
Message-ID: | 27874.1150813698@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
biuro(at)globeinphotos(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Markus Schaber | 2006-06-20 15:12:16 | Re: lowering priority automatically at connection |
Previous Message | biuro | 2006-06-20 12:39:32 | Curson prbolem |