Re: [PERFORM] Possible problem with DOMAIN evaluation?

> Looks like that IS the case; in fact, it gets that same plan even if I > don't specify ::country on the country string... > > This is obviously something that has changed _big time_ betwixt 7.3 and > 7.4... Several issues of this type have been fixed during 7.4, though there are a few left with

Re: [PERFORM] Mapping a database completly into Memory

> "BM" == Bruce Momjian <[EMAIL PROTECTED]> writes: BM> I think it all depends on your working set. Having shared memory be BM> smaller than you working set causes pages to have to be copied in from BM> the kernel buffers (not a huge problem, but a small penalty), while BM> having shared memo

[PERFORM] Help on my database performance

Hi! There: I ran the same explain analyze on two similar tables. However, the table with less data took much more time than the one with more data. Could anyone tell me what happened? Here is the explain analyze: explain analyze select productid from tfd_catalog; NOTICE: QUERY PLAN: Seq Scan on

Re: [PERFORM] postgresql.conf

So, maybe just a note on which parameters to increase if you have more RAM/CPU/I/O bandwidth in the big server example? Yes, that would be great. Actually I prefer rules of thumb and examples for each extreme. If possible a little note WHY the parameter should be tweaked, and what ef

[PERFORM] EXTERNAL storage and substring on long strings

Hello, 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: A freshly loaded database is VACUUM AN

Re: [PERFORM] Help on my database performance

On Thu, 31 Jul 2003 11:06:09 -0400, "Jianshuo Niu" <[EMAIL PROTECTED]> wrote: >I ran the same explain analyze on two similar tables. However, the table >with less data took much more time than the one with more data. Could anyone >tell me what happened? >Seq Scan on tfd_catalog (cost=0.00..43769.

Re: [PERFORM] Tuning PostgreSQL

> "RJ" == Ron Johnson <[EMAIL PROTECTED]> writes: RJ> On Tue, 2003-07-29 at 14:00, scott.marlowe wrote: RJ> Sounds like my kinda card! RJ> Is the cache battery-backed up? yep RJ> How much cache can you stuff in them? as per dell, the max is 128Mb, which was a bummer. -- =-=-=-=-=-=-

Re: [PERFORM] Moving postgresql.conf tunables into 2003...

[jumping in late due to vacation] On Thu, 3 Jul 2003 17:06:46 -0700, Sean Chittenden <[EMAIL PROTECTED]> wrote: >> is some other problem that needs to be solved. (I'd wonder about >> index correlation myself; we know that that equation is pretty >> bogus.) > >Could be. I had him create a multi-c

Re: [PERFORM] EXTERNAL storage and substring on long strings

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

Re: [PERFORM] EXTERNAL storage and substring on long strings

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

Re: [PERFORM] EXTERNAL storage and substring on long strings

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.

Re: [PERFORM] EXTERNAL storage and substring on long strings

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

[PERFORM] Odd performance results

I have a table with a 3 column key. I noticed that when I update a non-key field in a record of the table that the update was taking longer than I thought it should. After much experimenting I discovered that if I changed the data types of two of the key columns to FLOAT8 that I got vastly impr

[PERFORM] Odd explain estimate

Why is pgsql estimating a cost of 1 for retire_today in this query? I analyzed it, and there's nothing very odd about it, other than it's a temp table. BTW, I had to set enable_seqscan=false to get this, otherwise it wants to seqscan ogr_results, which is rather painful since it occupies 3

Re: [PERFORM] EXTERNAL storage and substring on long strings

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

Re: [PERFORM] EXTERNAL storage and substring on long strings

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?

Re: [PERFORM] EXTERNAL storage and substring on long strings

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

Re: [PERFORM] Odd explain estimate

On Thu, Jul 31, 2003 at 02:51:45PM -0500, Jim C. Nasby wrote: > Why is pgsql estimating a cost of 1 for retire_today in this > query? I analyzed it, and there's nothing very odd about it, other than > it's a temp table. > > BTW, I had to set enable_seqscan=false to get this, otherwise it w

Re: [PERFORM] Help on my database performance

Dear Manfred: Thank you so much for your response. vacuum full anaylze works! explain analyze select count(*) from tfd_catalog ; NOTICE: QUERY PLAN: explain analyze select count(*) from tfd_catalog ; NOTICE: QUERY PLAN: Aggregate (cost=15986.02..15986.02 rows=1 width=0) (actual time=1089.99..

Re: [PERFORM] EXTERNAL storage and substring on long strings

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

Re: [PERFORM] Odd performance results

"Medora Schauer" <[EMAIL PROTECTED]> writes: > I have a table with a 3 column key. I noticed that when I update a non-key field > in a record of the table that the update was taking longer than I thought it > should. After much experimenting I discovered that if I changed the data > types of two

Re: [PERFORM] EXTERNAL storage and substring on long strings

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

Re: [PERFORM] EXTERNAL storage and substring on long strings

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'

Re: [PERFORM] Help on my database performance

On Thu, 31 Jul 2003 16:08:11 -0400, "Jianshuo Niu" <[EMAIL PROTECTED]> wrote: >explain analyze select count(*) from tfd_catalog ; >NOTICE: QUERY PLAN: > >Aggregate (cost=15986.02..15986.02 rows=1 width=0) > (actual time=1089.99..1089.99 rows=1 loops=1) > -> Seq Scan on tfd_catalog (c

Re: [PERFORM] Odd performance results

Orignally there were but in the process of trying to figure out what is going on I stripped everything out of the database except the table being queried. > > "Medora Schauer" <[EMAIL PROTECTED]> writes: > > I have a table with a 3 column key. I noticed that when I > update a non-key field > >

[PERFORM] Views With Unions

This is stepping back quite a while; let me point people to the thread of 2003-02 where Mariusz Czu\x{0142}ada <[EMAIL PROTECTED]> was looking for a way of optimizing a VIEW that was a UNION. The subject has come up a few tim

Re: [PERFORM] Views With Unions

On Thu, 31 Jul 2003, Christopher Browne wrote: > select * from log_table where request_time between 'june 11 2003' and >'june 12 2003'; > > returns a plan: > Subquery Scan log_table (cost=0.00..10950.26 rows=177126 width=314) > -> Append

Re: [PERFORM] Odd explain estimate

On Thu, Jul 31, 2003 at 04:59:21PM -0400, Andrew Sullivan wrote: > On Thu, Jul 31, 2003 at 02:51:45PM -0500, Jim C. Nasby wrote: > If you really needed to set enable_seqscan=false (did you really? > Are you sure that's not the cheapest way?), you might want to > investigate expainding the statisti

Re: [PERFORM] EXTERNAL storage and substring on long strings

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

Re: [PERFORM] Views With Unions

Stephan Szabo wrote: On Thu, 31 Jul 2003, Christopher Browne wrote: select * from log_table where request_time between 'june 11 2003' and 'june 12 2003'; returns a plan: Subquery Scan log_table (cost=0.00..10950.26 rows=177126 width=314) ->