2004 19:26:39 -0500
Subject: Re: [PERFORM] Strange (?) Index behavior?
To: [EMAIL PROTECTED]
On Fri, 12 Nov 2004 17:35:00 -0500, Tom Lane [EMAIL PROTECTED] wrote:
Allen Landsidel [EMAIL PROTECTED] writes:
On Thu, 11 Nov 2004 16:41:51 -0500, Tom Lane [EMAIL PROTECTED] wrote:
Allen Landsidel
On Thu, 11 Nov 2004 16:41:51 -0500, Tom Lane [EMAIL PROTECTED] wrote:
Allen Landsidel [EMAIL PROTECTED] writes:
Clustering is really unworkable in this situation.
Nonetheless, please do it in your test scenario, so we can see if it has
any effect or not.
It did not, not enough to measure
Allen Landsidel [EMAIL PROTECTED] writes:
On Thu, 11 Nov 2004 16:41:51 -0500, Tom Lane [EMAIL PROTECTED] wrote:
Allen Landsidel [EMAIL PROTECTED] writes:
Clustering is really unworkable in this situation.
Nonetheless, please do it in your test scenario, so we can see if it has
any effect or
On Fri, 12 Nov 2004 17:35:00 -0500, Tom Lane [EMAIL PROTECTED] wrote:
Allen Landsidel [EMAIL PROTECTED] writes:
On Thu, 11 Nov 2004 16:41:51 -0500, Tom Lane [EMAIL PROTECTED] wrote:
Allen Landsidel [EMAIL PROTECTED] writes:
Clustering is really unworkable in this situation.
Ok, you thought maybe this thread died or got abandoned in the face of
all the senseless trolling and spam going on.. you were wrong.. ;)
I thought though I'd start over trying to explain what's going on.
I've gone through some dumps, and recreation of the database with some
different filesystem
Allen,
Ok, you thought maybe this thread died or got abandoned in the face of
all the senseless trolling and spam going on.. you were wrong.. ;)
I thought though I'd start over trying to explain what's going on.
I've gone through some dumps, and recreation of the database with some
On Thu, 11 Nov 2004 10:52:43 -0800, Josh Berkus [EMAIL PROTECTED] wrote:
Allen,
Ok, you thought maybe this thread died or got abandoned in the face of
all the senseless trolling and spam going on.. you were wrong.. ;)
I thought though I'd start over trying to explain what's going on.
Allen Landsidel [EMAIL PROTECTED] writes:
QUERY PLAN
---
Index Scan using sname_unique on testtable (cost=0.00..34453.74
rows=8620 width=20) (actual
On 11 Nov 2004 15:49:46 -0500, Greg Stark [EMAIL PROTECTED] wrote:
Allen Landsidel [EMAIL PROTECTED] writes:
QUERY PLAN
---
Index Scan using
Allen Landsidel [EMAIL PROTECTED] writes:
Clustering is really unworkable in this situation.
Nonetheless, please do it in your test scenario, so we can see if it has
any effect or not.
The speed you're getting works out to about 7.2 msec/row, which would be
about right if every single row fetch
---
Index Scan using sname_unique on testtable (cost=0.00..34453.74
rows=8620 width=20) (actual time=77.004..537065.079 rows=74612
loops=1)
Index Cond:
Hey people, long while since I posted here, but I'm having an index
issue that looks on the surface to be a little strange.
I have a text field that I'm trying to query on in a table with
millions of rows. Stupid I know, but a fairly common stupid thing to
try to do.
For some reason it's a
For some reason it's a requirement that partial wildcard
searches are done on this field, such as SELECT ... WHERE
field LIKE 'A%'
I thought an interesting way to do this would be to simply
create partial indexes for each letter on that field, and it
works when the query matches the
It seems to me that a query saying SELECT column FROM table WHERE
column LIKE 'AA%'; should be just as fast or very close to the first
case up above. However, explain tells me that this query is not using
the index above, which is what's not making sense to me.
It looks for an exact
On Fri, 05 Nov 2004 10:07:38 -0500, Rod Taylor [EMAIL PROTECTED] wrote:
It seems to me that a query saying SELECT column FROM table WHERE
column LIKE 'AA%'; should be just as fast or very close to the first
case up above. However, explain tells me that this query is not using
the index
On Fri, 5 Nov 2004 10:32:43 -0500, Allen Landsidel [EMAIL PROTECTED] wrote:
On Fri, 05 Nov 2004 10:07:38 -0500, Rod Taylor [EMAIL PROTECTED] wrote:
It seems to me that a query saying SELECT column FROM table WHERE
column LIKE 'AA%'; should be just as fast or very close to the first
On Fri, Nov 05, 2004 at 09:39:16 -0500,
Allen Landsidel [EMAIL PROTECTED] wrote:
For some reason it's a requirement that partial wildcard searches are
done on this field, such as SELECT ... WHERE field LIKE 'A%'
I thought an interesting way to do this would be to simply create
partial
On Fri, 5 Nov 2004 11:51:59 -0600, Bruno Wolff III [EMAIL PROTECTED] wrote:
On Fri, Nov 05, 2004 at 09:39:16 -0500,
Allen Landsidel [EMAIL PROTECTED] wrote:
For some reason it's a requirement that partial wildcard searches are
done on this field, such as SELECT ... WHERE field LIKE 'A%'
With that many rows, and a normal index on the field,
postgres figures the best option for say I% is not an index
scan, but a sequential scan on the table, with a filter --
quite obviously this is slow as heck, and yes, I've run
analyze several times and in fact have the vacuum analyze
Allen Landsidel [EMAIL PROTECTED] writes:
With that many rows, and a normal index on the field, postgres figures
the best option for say I% is not an index scan, but a sequential
scan on the table, with a filter -- quite obviously this is slow as
heck, and yes, I've run analyze several times
On Fri, 05 Nov 2004 14:57:40 -0500, Tom Lane [EMAIL PROTECTED] wrote:
Allen Landsidel [EMAIL PROTECTED] writes:
With that many rows, and a normal index on the field, postgres figures
the best option for say I% is not an index scan, but a sequential
scan on the table, with a filter -- quite
On Fri, 5 Nov 2004 18:34:23 -, Matt Clark [EMAIL PROTECTED] wrote:
With that many rows, and a normal index on the field,
postgres figures the best option for say I% is not an index
scan, but a sequential scan on the table, with a filter --
quite obviously this is slow as heck, and yes,
Allen Landsidel [EMAIL PROTECTED] writes:
With seqscan enabled however, AB% will use the index, but A% will not.
The estimated cost for the query is much higher without the partial
indexes than it is with them, and the actual runtime of the query is
definitely longer without the partial
On Fri, 05 Nov 2004 16:08:56 -0500, Tom Lane [EMAIL PROTECTED] wrote:
Allen Landsidel [EMAIL PROTECTED] writes:
With seqscan enabled however, AB% will use the index, but A% will not.
The estimated cost for the query is much higher without the partial
indexes than it is with them, and the
Matt Clark [EMAIL PROTECTED] writes:
Well, 74000/7600 ~= 0.1%, way less than 1/26, so no surprise that an
indexscan is better, and also no surprise that the planner can't know
that I is such an uncommon initial char.
But it *can* know that, at least given adequate ANALYZE statistics.
I'm
On Fri, 05 Nov 2004 23:04:23 -0500, Tom Lane [EMAIL PROTECTED] wrote:
Matt Clark [EMAIL PROTECTED] writes:
Well, 74000/7600 ~= 0.1%, way less than 1/26, so no surprise that an
indexscan is better, and also no surprise that the planner can't know
that I is such an uncommon initial char.
26 matches
Mail list logo
