Re: [PERFORM] multi-line copy (was: Re: COPY Hacks)

Quoting Greg Sabino Mullane <[EMAIL PROTECTED]>: > > Using COPY ... FROM STDIN via the Perl DBI (DBD::Pg) interface, > > I accidentally strung together several \n-terminated input lines, > > and sent them to the server with a single "putline". > ... > > So. Is it a feechur? Worth stress-testing?

Re: COPY Hacks (WAS: RE: [PERFORM] Postgresql vs SQLserver for this application ?)

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > Using COPY ... FROM STDIN via the Perl DBI (DBD::Pg) interface, > I accidentally strung together several \n-terminated input lines, > and sent them to the server with a single "putline". ... > So. Is it a feechur? Worth stress-testing? Could be VER

Re: [PERFORM] help on explain analyse in psql 7.1.3 (linux)

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Christopher Kings-Lynne wrote: > Explain analyze was added in 7.2 - you really need to upgrade... > > You can use \timing in psql to get an approximation... Actually, \timing was not added until 7.2 either! So, the original poster really, really nee

Re: [PERFORM] Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)

Mischa <[EMAIL PROTECTED]> writes: > Quoting Tom Lane <[EMAIL PROTECTED]>: >> WHERE a.x > b.y AND a.x < 42 > Out of curiosity, will the planner induce "b.y < 42" out of this? No. There's some smarts about transitive equality, but none about transitive inequalities. Offhand I'm not sure if it'd

Re: [HACKERS] Recognizing range constraints (was Re: [PERFORM] Plan for relatively simple query seems to be very inefficient)

On Wed, Apr 06, 2005 at 06:35:10PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > On Wed, Apr 06, 2005 at 06:09:37PM -0400, Tom Lane wrote: > >> Can anyone suggest a more general rule? Do we need for example to > >> consider whether the relation membership is the same in t

Re: [PERFORM] Building postmaster with Profiling Support WAS "Tweaking a C

The recompile was done by the sysadmin, but I believe the flags are -pg -DLINUX_PROFILING for profiling, and -g for debug symbols. This leaves gmon.out files around, which you can then do a "gprof /usr/bin/postmaster gmon.out" to see whats going on. My problem is that this gives me data on what

Re: [PERFORM] Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)

Quoting Tom Lane <[EMAIL PROTECTED]>: > Yeah, the whole thing is only a heuristic anyway. I've been coming > around to the view that relation membership shouldn't matter, because > of cases like > > WHERE a.x > b.y AND a.x < 42 > > which surely should be taken as a range constraint. Out

Re: [PERFORM] Any way to speed this up?

Joel Fradkin wrote: Here is the result after putting it back to 4 the original value (I had done that prior to your suggestion of using 2 or 3) to see what might change. I also vacummed and thought I saw records deleted in associate, which I found odd as this is a test site and no new records were

Re: [PERFORM] Any way to speed this up?

Here is the result after putting it back to 4 the original value (I had done that prior to your suggestion of using 2 or 3) to see what might change. I also vacummed and thought I saw records deleted in associate, which I found odd as this is a test site and no new records were added or deleted. "

Re: [PERFORM] Any way to speed this up?

Joel Fradkin wrote: shared_buffers = 8000 # min 16, at least max_connections*2, 8KB each work_mem = 8192#1024# min 64, size in KB max_fsm_pages = 3 # min max_fsm_relations*16, 6 bytes each effective_cache_size = 4 #1000 # typically 8KB each random_page_c

Re: [PERFORM] Any way to speed this up?

"Joel Fradkin" <[EMAIL PROTECTED]> writes: > random_page_cost = 1.2#4 # units are one sequential page > fetch cost That is almost certainly overoptimistic; it's causing the planner to use indexscans when it shouldn't. Try 2 or 3 or thereabouts. regards, tom l

Re: [PERFORM] Any way to speed this up?

shared_buffers = 8000 # min 16, at least max_connections*2, 8KB each work_mem = 8192#1024# min 64, size in KB max_fsm_pages = 3 # min max_fsm_relations*16, 6 bytes each effective_cache_size = 4 #1000 # typically 8KB each random_page_cost = 1.2#4

Re: [PERFORM] How to improve db performance with $7K?

Yep, that's it, as well as increased quality control. I found this from Seagate: http://www.seagate.com/content/docs/pdf/whitepaper/D2c_More_than_Interface_ATA_vs_SCSI_042003.pdf With this quote (note that ES stands for Enterprise System and PS stands for Personal System): There is significantl

Re: [PERFORM] Any way to speed this up?

John Arbash Meinel <[EMAIL PROTECTED]> writes: >> " -> Sort (cost=59478.03..59909.58 rows=172618 width=75) (actual >> time=46844.000..46985.000 rows=159960 loops=1)" >> >> "Sort Key: a.locationid" >> > This sort actually isn't taking very long. It starts at 46800 and runs > until 4700

Re: [PERFORM] Any way to speed this up?

"Joel Fradkin" <[EMAIL PROTECTED]> writes: > Running this explain on windows box, but production on linux both 8.0.1 Are you using any nondefault optimizer settings? The vast bulk of the time is going into the indexscan on tblassociate (almost 42 out of the 48 seconds), and I'm a bit surprised it

Re: [PERFORM] Building postmaster with Profiling Support WAS "Tweaking a C Function I wrote"

"Mohan, Ross" <[EMAIL PROTECTED]> writes: > Is compiling postmaster with profiling support just a flag > in the build/make? Or is there something more involved? cd .../src/backend make PROFILE="-pg -DLINUX_PROFILE" all reinstall binary You don't need -DLINUX_PROFILE if not on Linux, of course.

Re: [PERFORM] How to improve db performance with $7K?

Based on the reading I'm doing, and somebody please correct me if I'm wrong, it seems that SCSI drives contain an on disk controller that has to process the tagged queue. SATA-I doesn't have this. This additional controller, is basicaly an on board computer that figures out the best order in whic

Re: [PERFORM] help on explain analyse in psql 7.1.3 (linux)

Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: >> I didn't find option analyse in explain command.. >> >> how to get time taken by SQL procedure/query? > Explain analyze was added in 7.2 - you really need to upgrade... > You can use \timing in psql to get an approximation... 7.1 psql hasn'

Re: [PERFORM] Any way to speed this up?

Joel Fradkin wrote: Running this explain on windows box, but production on linux both 8.0.1 The MSSQL is beating me out for some reason on this query. The linux box is much more powerful, I may have to increase the cache, but I am pretty sure its not an issue yet. It has 8 gig internal memory any r

Re: [PERFORM] How to improve db performance with $7K?

Another simple question: Why is SCSI more expensive? After the eleventy-millionth controller is made, it seems like SCSI and SATA are using a controller board and a spinning disk. Is somebody still making money by licensing SCSI technology? Rick [EMAIL PROTECTED] wrote on 04/06/2005 11:58:33 PM

Re: [PERFORM] Any way to speed this up?

On Thu, 07 Apr 2005 11:13:57 -0400, Joel Fradkin wrote [snip] > " -> Sort (cost=393.76..394.61 rows=338 width=48) (actual > time=62.000..62.000 rows=441 loops=1)" > > "Sort Key: l.locationid" > > "-> Index Scan using ix_location on tbllocation l > > (cost=0.00..379.56 rows=33

[PERFORM] Building postmaster with Profiling Support WAS "Tweaking a C Function I wrote"

Adam - Is compiling postmaster with profiling support just a flag in the build/make? Or is there something more involved? I'd like to be able to do this in the future and so am curious about means/methods. If this is a RTFM, just let me know that (am currently Reading The F Manual), but if y

[PERFORM] Any way to speed this up?

Running this explain on windows box, but production on linux both 8.0.1 The MSSQL is beating me out for some reason on this query. The linux box is much more powerful, I may have to increase the cache, but I am pretty sure its not an issue yet. It has 8 gig internal memory any recommenda

Re: [PERFORM] Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)

Bruno Wolff III <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> wrote: >> Can anyone suggest a more general rule? > I think it makes sense to guess that a smaller fraction of the rows will > be returned when a column value is bounded above and below than if it > is only bounded on one

Re: [PERFORM] Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)

On Wed, Apr 06, 2005 at 18:09:37 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > > Can anyone suggest a more general rule? Do we need for example to > consider whether the relation membership is the same in two clauses > that might be opposite sides of a range restriction? It seems like > >

[PERFORM] 4 way JOIN using aliases

Hi All, Thanks to all on the NOVICE list that gave me help I now have a query running that returns the results I am after. :-) Now of course I want it to run faster. Currently it clocks in at ~160ms. I have checked over the indexes and I belive that the tables are indexed properly. The largest

Re: [PERFORM] COPY Hacks (WAS: RE: Postgresql vs SQLserver for this application ?)

In article <[EMAIL PROTECTED]>, Mischa <[EMAIL PROTECTED]> writes: > This thread seems to be focusing in on COPY efficiency, > I'd like to ask something I got no answer to, a few months ago. > Using COPY ... FROM STDIN via the Perl DBI (DBD::Pg) interface, > I accidentally strung together several

Re: [PERFORM] How to improve db performance with $7K?

A good one page discussion on the future of SCSI and SATA can be found in the latest CHIPS (The Department of the Navy Information Technology Magazine, formerly CHIPS AHOY) in an article by Patrick G. Koehler and Lt. Cmdr. Stan Bush. Click below if you don't mind being logged visiting Space and Na

Re: [PERFORM] help on explain analyse in psql 7.1.3 (linux)

hi, thanks for immediate response.. regards, stp.. On Thu, 7 Apr 2005, Christopher Kings-Lynne wrote: > > I didn't find option analyse in explain command.. > > > > how to get time taken by SQL procedure/query? > > Explain analyze was added in 7.2 - you really need to upgrade... > > Y

[PERFORM] help on time calculation

hi, how to find the time taken by an query/stored procedure? I am using psql 7.1.3 in linux 7.2 how to execute 'explain analyse' in the psql? Is it supported at 7.1.3 ? looking forward for replies.. regards, stp. ---(end of broadcast)--- TIP

Re: [PERFORM] help on time calculation

how to find the time taken by an query/stored procedure? In psql, use \timing for an approximate time. I am using psql 7.1.3 in linux 7.2 how to execute 'explain analyse' in the psql? Is it supported at 7.1.3 ? Explain analyze is NOT supported in PostgreSQL 7.1. You really should upgrade your

Re: [PERFORM] help on explain analyse in psql 7.1.3 (linux)

I didn't find option analyse in explain command.. how to get time taken by SQL procedure/query? Explain analyze was added in 7.2 - you really need to upgrade... You can use \timing in psql to get an approximation... Chris ---(end of broadcast)--- TIP

[PERFORM] help on explain analyse in psql 7.1.3 (linux)

hi, I am using psql 7.1.3 I didn't find option analyse in explain command.. how to get time taken by SQL procedure/query? regards, stp.. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate s