On Wed, Jun 06, 2007 at 11:23:13PM +0100, James Mansion wrote:
> [EMAIL PROTECTED] wrote:
> >What is a real life example where an intelligent and researched
> >database application would issue a like or ilike query as their
> >primary condition in a situation where they expected very high
> >select
[EMAIL PROTECTED] wrote:
What is a real life example where an intelligent and researched
database application would issue a like or ilike query as their
primary condition in a situation where they expected very high
selectivity?
In my case the canonical example is to search against textual key
Gregory Stark wrote:
"Richard Huxton" <[EMAIL PROTECTED]> writes:
Now you and I can look at a substring and probably make a good guess how common
it is (assuming we know the targets are British surnames or Japanese towns). PG
needs one number - or rather, it picks one number for each length of
"Richard Huxton" <[EMAIL PROTECTED]> writes:
> Now you and I can look at a substring and probably make a good guess how
> common
> it is (assuming we know the targets are British surnames or Japanese towns).
> PG
> needs one number - or rather, it picks one number for each length of
> search-str
PFC wrote:
None of which address the question of what plan PG should produce for:
SELECT * FROM bigtable WHERE foo LIKE 's%'
Ah, this one already uses the btree since the '%' is at the end.
My point is that a search like this will yield too many results to
be useful to the user anyway,
[EMAIL PROTECTED] wrote:
On Fri, May 25, 2007 at 04:35:22PM +0100, Richard Huxton wrote:
I notice you did not provide a real life example as requested. :-)
OK - any application that allows user-built queries: foo>
Want another? Any application that has a "search by name" box - users
can (and
None of which address the question of what plan PG should produce for:
SELECT * FROM bigtable WHERE foo LIKE 's%'
Ah, this one already uses the btree since the '%' is at the end.
My point is that a search like this will yield too many results to be
useful to the user anyway, so optim
PFC wrote:
OK - any application that allows user-built queries: foo>
Want another? Any application that has a "search by name" box - users
can (and do) put one letter in and hit enter.
Unfortunately you don't always have control over the selectivity of
queries issued.
-*- HOW TO MA
[EMAIL PROTECTED] wrote:
I am speaking of contains, as contains is the one that was said to
require a seqscan. I am questioning why it requires a seqscan. The
claim was made that with MVCC, the index is insufficient to check
for visibility and that the table would need to be accessed anyways,
th
On Fri, May 25, 2007 at 04:35:22PM +0100, Richard Huxton wrote:
> >I notice you did not provide a real life example as requested. :-)
> OK - any application that allows user-built queries: foo>
> Want another? Any application that has a "search by name" box - users
> can (and do) put one letter
OK - any application that allows user-built queries: foo>
Want another? Any application that has a "search by name" box - users
can (and do) put one letter in and hit enter.
Unfortunately you don't always have control over the selectivity of
queries issued.
-*- HOW TO MAKE A
[EMAIL PROTECTED] wrote:
On Fri, May 25, 2007 at 09:13:25AM +0100, Richard Huxton wrote:
[EMAIL PROTECTED] wrote:
And since it's basically impossible to know the selectivity of this kind
of where condition, I doubt the planner would ever realistically want to
choose that plan anyway because of
On Fri, May 25, 2007 at 09:13:25AM +0100, Richard Huxton wrote:
> [EMAIL PROTECTED] wrote:
> >>And since it's basically impossible to know the selectivity of this kind
> >>of where condition, I doubt the planner would ever realistically want to
> >>choose that plan anyway because of its poor worst-
[EMAIL PROTECTED] wrote:
And since it's basically impossible to know the selectivity of this kind
of where condition, I doubt the planner would ever realistically want to
choose that plan anyway because of its poor worst-case behavior.
What is a real life example where an intelligent and resear
PG could scan the index looking for matches first and only load the
actual rows if it found a match, but that could only be a possible win
if there were very few matches, because the difference in cost between a
full index scan and a sequential scan would need to be greater than the
cost of rand
Alvaro Herrera wrote:
>> Just out of curiosity: Does Postgress store a duplicate of the data in the
index, even for long strings? I thought indexes only had to store the
string up to the point where there was no ambiguity, for example, if I have
"missing", "mississippi" and "misty", the index
On Thu, May 24, 2007 at 02:02:40PM -0700, Mark Lewis wrote:
> PG could scan the index looking for matches first and only load the
> actual rows if it found a match, but that could only be a possible win
> if there were very few matches, because the difference in cost between a
> full index scan and
Craig James wrote:
> Mark Lewis wrote:
>
> >PG could scan the index looking for matches first and only load the
> >actual rows if it found a match, but that could only be a possible win
> >if there were very few matches, because the difference in cost between a
> >full index scan and a sequential
Mark Lewis wrote:
PG could scan the index looking for matches first and only load the
actual rows if it found a match, but that could only be a possible win
if there were very few matches, because the difference in cost between a
full index scan and a sequential scan would need to be greater tha
On Thu, 2007-05-24 at 21:54 +0100, James Mansion wrote:
> > If Sybase is still like SQL Server (or the other way around), it *may*
> > end up scanning the index *IFF* the index is a clustered index. If it's
> > a normal index, it will do a sequential scan on the table.
> >
> >
> Are you sure its
If Sybase is still like SQL Server (or the other way around), it *may*
end up scanning the index *IFF* the index is a clustered index. If it's
a normal index, it will do a sequential scan on the table.
Are you sure its not covered? Have to check at work - but I'm off next
week so it'll hav
James Mansion wrote:
> Alexander Staubo wrote:
>> On 5/23/07, Andy <[EMAIL PROTECTED]> wrote:
>>> An example would be:
>>> SELECT * FROM table
>>> WHERE name like '%john%' or street like
>>> '%srt%'
>>>
>>> Anyway, the query planner always does seq scan on the whole tab
Alexander Staubo wrote:
On 5/23/07, Andy <[EMAIL PROTECTED]> wrote:
An example would be:
SELECT * FROM table
WHERE name like '%john%' or street like
'%srt%'
Anyway, the query planner always does seq scan on the whole table and
that
takes some time. How can this
pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] LIKE search and performance
>
> Andy wrote:
> > Hi,
> >
> > I have a table with varchar and text columns, and I have to search
> > through these text in the whole table.
>
Andy wrote:
Hi,
I have a table with varchar and text columns, and I have to search
through these text in the whole table.
An example would be:
SELECT * FROM table
WHERE name like '%john%' or street like '%srt%'
Anyway, the query planner always does seq scan on
On 5/23/07, Andy <[EMAIL PROTECTED]> wrote:
An example would be:
SELECT * FROM table
WHERE name like '%john%' or street like '%srt%'
Anyway, the query planner always does seq scan on the whole table and that
takes some time. How can this be optimized or made in anoth
Am 23.05.2007 um 09:08 schrieb Andy:
I have a table with varchar and text columns, and I have to search
through these text in the whole table.
An example would be:
SELECT * FROM table
WHERE name like '%john%' or street
like '%srt%'
Anyway, the query planner al
Andy wrote:
SELECT * FROM table
WHERE name like '%john%' or street like '%srt%'
Anyway, the query planner always does seq scan on the whole table and that
takes some time. How can this be optimized or made in another way to be
faster?
I tried to make indexes on t
Hi,
I have a table with varchar and text columns, and I have to search through
these text in the whole table.
An example would be:
SELECT * FROM table
WHERE name like '%john%' or street like '%srt%'
Anyway, the query planner always does seq scan on the whole tab
29 matches
Mail list logo
