On Sat, May 27, 2006 at 11:52:40AM -0400, Tom Lane wrote:
> Martijn van Oosterhout 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 VAC
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 retur
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
> tuple
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
Martijn van Oosterhout 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).
regards,
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 i
Martijn van Oosterhout 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 a heck of
> 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
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
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
> precede
"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 b
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 like
These are two confusing issues.
One is the use of a leading percent sign.
What Tom pointed out was with a bound parameter the planner can't
make any assumptions about indexes.
Leading percent signs can be made to use indexes by creating a
functional index on the column which reverses the o
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
ind
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 boun
> 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
"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, w
"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 seein
PG-Hackers,I got the following picture:detran=# \d sa_dut.tb_usuario Table "sa_dut.tb_usuario" Column | Type | Modifiers-+-+---
numprocesso | bigint
19 matches
Mail list logo
