Scott Cain <[EMAIL PROTECTED]> writes:
> A few days ago, I asked for advice on speeding up substring queries on
> the GENERAL mailing list. Joe Conway helpfully pointed out the ALTER
> TABLE STORAGE EXTERNAL documentation. After doing the alter,
> the queries got slower! Here is the background:
> snipped much discussion about EXTERNAL storage and substring speed
Joe and Tom,
Thanks for all of your help; I went back to my (nearly) production
database, and executed the `update feature set residues = residues
||'';` and then redid my benchmark. Here's a summary of the results:
substr in
Joe Conway <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Ah-hah, I've sussed it ... you didn't actually change the storage
>> representation. You wrote:
> Yeah, I came to the same conclusion this morning (update longdna set dna
> = dna || '';), but it still seems that the chunked table is ver
Tom Lane wrote:
Scott Cain <[EMAIL PROTECTED]> writes:
A few days ago, I asked for advice on speeding up substring queries on
the GENERAL mailing list. Joe Conway helpfully pointed out the ALTER
TABLE STORAGE EXTERNAL documentation. After doing the alter,
the queries got slower! Here is the bac
Hello,
Note: there is a SQL question way at the bottom of this narrative :-)
Last week I asked about doing substring operations on very long strings
(>10 million characters). I was given a suggestion to use EXTERNAL
storage on the column via the ALTER TABLE ... SET STORAGE command. In
one test
So it is possible that if I had a fast scsi drive, the performance might
be better?
On Thu, 2003-07-31 at 16:31, Joe Conway wrote:
> Scott Cain wrote:
> > Index Scan using feature_pkey on feature (cost=0.00..3.01 rows=1
> > width=153) (actual time=954.13..954.14 rows=1 loops=1)
> >Index Cond
On Thu, 2003-07-31 at 15:31, Joe Conway wrote:
> Scott Cain wrote:
> > Index Scan using feature_pkey on feature (cost=0.00..3.01 rows=1
> > width=153) (actual time=954.13..954.14 rows=1 loops=1)
> >Index Cond: (feature_id = 1)
> > Total runtime: 954.26 msec
> > (3 rows)
> >
> > Whoa! That'
Joe,
I'm working on the comparison--I think the best way to do it is to
reload the original data into a new database and compare them, so it
will take a while.
I have tuned postgresql.conf according to the page that everybody around
here seems to cite. I'll probably post back tomorrow with anothe
Scott Cain wrote:
I am not against recompiling. I am currently using an RPM version, but
I could probably recompile; the compilation is probably straight forward
(adding something like `--with_profiling` to ./configure), but how
straight forward is actually doing the profiling? Is there a documen
Scott Cain <[EMAIL PROTECTED]> writes:
> I see, encoding is a per database option. Since I've never set it, all
> my databases use sql_ascii.
Okay, then you've dodged the obvious bullet; time to try profiling I
guess. The way I usually do it is (given a clean, configured source
tree):
c
Scott Cain wrote:
So it is possible that if I had a fast scsi drive, the performance might
be better?
Faster drives are always better ;-)
Did you try the comparison with shorter substrings? Also, maybe not
related to your specific question, but have you tuned any other
postgresql.conf settings?
Scott Cain <[EMAIL PROTECTED]> writes:
> explain analyze select substring(residues from 100 for 2)
> from feature where feature_id=1;
> where feature is a table with ~3 million rows, and residues is a text
> column, where for the majority of the rows of feature, it is null, for a
> large m
Scott Cain <[EMAIL PROTECTED]> writes:
>> (BTW, if you are using a multibyte database encoding, then that's your
>> problem right there --- the optimization is practically useless unless
>> character and byte indexes are the same.)
> I shouldn't be, but since it is an RPM, I can't be sure.
Look a
Scott Cain wrote:
Index Scan using feature_pkey on feature (cost=0.00..3.01 rows=1
width=153) (actual time=954.13..954.14 rows=1 loops=1)
Index Cond: (feature_id = 1)
Total runtime: 954.26 msec
(3 rows)
Whoa! That's not what I expected, the time to do the query got more
that twice as long.
On Thu, 2003-07-31 at 16:32, Tom Lane wrote:
> Scott Cain <[EMAIL PROTECTED]> writes:
> >> (BTW, if you are using a multibyte database encoding, then that's your
> >> problem right there --- the optimization is practically useless unless
> >> character and byte indexes are the same.)
>
> > I shoul
On Thu, 2003-07-31 at 15:44, Tom Lane wrote:
> Scott Cain <[EMAIL PROTECTED]> writes:
> > explain analyze select substring(residues from 100 for 2)
> > from feature where feature_id=1;
>
> > where feature is a table with ~3 million rows, and residues is a text
> > column, where for the maj
16 matches
Mail list logo
