On Sat, May 27, 2006 at 11:52:40AM -0400, Tom Lane wrote:
Martijn van Oosterhout kleptog@svana.org writes:
How about the suggestion of using a sequential index scan like the
recent changes to VACUUM in the case that there are no regular index
quals?
Nonstarter (hint: the solution we
On Sat, 27 May 2006, Martijn van Oosterhout wrote:
On Sat, May 27, 2006 at 10:57:05AM -0400, Tom Lane wrote:
* Up to now, the only functions directly invoked by an index AM were
members of index opclasses; and since opclasses can only be defined by
superusers, there was at least some basis for
On Sun, May 28, 2006 at 10:43:18PM +0300, Heikki Linnakangas wrote:
I don't know the planner internals, so this might not make any sense at
all, but how about having separate index scan and fetch nodes. Index scan
would return index tuples and fetch would get the corresponding heap
tuples.
Heikki Linnakangas [EMAIL PROTECTED] writes:
On Sat, 27 May 2006, Martijn van Oosterhout wrote:
Actually, for a first pass I was considering doing it within the
nodeIndexScan.c/nodeBitmapScan.c and not within the AM at all. But I
just remembered, the index interface has no way to return the
Martijn van Oosterhout kleptog@svana.org writes:
On Fri, May 26, 2006 at 11:38:41AM -0500, Jim C. Nasby wrote:
Also, might a bitmap scan be a win for the %string case? Presumably it's
much faster to find matching rows via an index and then go back into the
heap for them; unless you're matching
On Sat, May 27, 2006 at 10:57:05AM -0400, Tom Lane wrote:
* Up to now, the only functions directly invoked by an index AM were
members of index opclasses; and since opclasses can only be defined by
superusers, there was at least some basis for trusting the functions
to behave sanely. But if
Martijn van Oosterhout kleptog@svana.org writes:
How about the suggestion of using a sequential index scan like the
recent changes to VACUUM in the case that there are no regular index
quals?
Nonstarter (hint: the solution we found for VACUUM assumes there can
be only one).
On Fri, May 26, 2006 at 11:38:41AM -0500, Jim C. Nasby wrote:
select * from table where field like 'THE NAME%'; -- index scan
select * from table where field like '%THE NAME%'; -- seq scan
select * from table where field like :bind_param; -- seq scan (always)
How difficult would it be to
On Thu, May 25, 2006 at 08:41:17PM -0300, Rodrigo Hjort wrote:
I think more exactly, the planner can't possibly know how to plan an
indexscan with a leading '%', because it has nowhere to start.
The fact is that index scan is performed on LIKE expression on a string
not
preceded by '%',
I'm not used to the PG Internals. But let me see if I understood that.The LIKE operator, when applied on a static string and it is not preceded by '%', causes the planner to search for some indexes in the table in order to make a index scan. Otherwise,
i.e. using leading '%' on static text or
On Thu, May 25, 2006 at 02:18:10PM -0300, Rodrigo Hjort wrote:
make a index scan. Otherwise, i.e. using leading '%' on static text or bound
paremeter, makes the planner always do a sequential scan. Is that the
scenario?
I think more exactly, the planner can't possibly know how to plan an
I think more exactly, the planner can't possibly know how to plan anindexscan with a leading '%', because it has nowhere to start.
The fact is that index scan is performed on LIKE _expression_ on a string not preceded by '%', except when bound parameter is used.
select * from table where field
Rodrigo Hjort [EMAIL PROTECTED] writes:
I think more exactly, the planner can't possibly know how to plan an
indexscan with a leading '%', because it has nowhere to start.
The fact is that index scan is performed on LIKE expression on a string not
preceded by '%', except when bound
Tom Lane [EMAIL PROTECTED] wrote
Yeah. The LIKE index optimization depends on seeing a constant LIKE
pattern at plan time --- otherwise the planner doesn't know what
indexscan parameters to generate. So a bound-parameter query loses.
AFAICS the problem is not restricted to LIKE, we can
AFAICS the problem is not restricted to LIKE, we can easily find a lot
of
similar problems caused by the actual parameters. For example, SeqScan
vs.
IndexScan vs. BitmapIndexScan for a range query. So an improvement is
definitely needed.
Another way is to generate a plan on the fly. What we
PG-Hackers,I got the following picture:detran=# \d sa_dut.tb_usuario Table sa_dut.tb_usuario Column | Type | Modifiers-+-+---
numprocesso | bigint | not nullnome | character varying(44) |nomemae | character varying(44) |datanascimento |
Rodrigo Hjort [EMAIL PROTECTED] writes:
What happens is that only the 004 block uses the index! The 002 code,
which also has no leading percent, does a sequential scan. The difference
between them is that 002 uses bind parameters.
Yeah. The LIKE index optimization depends on seeing a constant
17 matches
Mail list logo
