Re: [PERFORM] [SQL] two queryes in a single tablescan

esults in a faster way. PostgreSQL 8.3 contains great improvements in this area, you can simply start the selects from concurrent connections, and the backend will synchronize the scans. Regards, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Softwar

Re: [PERFORM] PostgreSQL underestimates sorting

mple configuration, there are others on different machines where it turns out that forcing index usage leads to faster queries, and less overall ressource consumption. (Or, at least, faster delivery of the first part of the result so the application can begin to process it asynchroneously). Thank

Re: [PERFORM] PostgreSQL underestimates sorting

Hi, Steinar, Steinar H. Gunderson wrote: > On Wed, Nov 22, 2006 at 11:17:23AM +0100, Markus Schaber wrote: >> The Backend allocates gigs of memory (we've set sort_mem to 1 gig), and >> then starts spilling out more Gigs of temporary data to the disk. > > How much RAM

[PERFORM] PostgreSQL underestimates sorting

irrelevant. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 9: In vers

Re: [PERFORM] Slow SELECT on three or more clients

s your free space map setting, the statistics targets, and other config options tuned to fit your environment? - Maybe a VACUUM FULL or a CLUSTER command may help you. > for a single select with one field in one resultset, is 0.86 seconds normal? That depends on the circumstances. Markus -- M

Re: [PERFORM] Easy read-heavy benchmark kicking around?

on, and if not, do a "time dd if=/your/device of=/dev/null bs=1M" on the partition. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org --

Re: [PERFORM] Best COPY Performance

Hi, Craig, Craig A. James wrote: >> Would dblink() help in any way? > > It might if perl wasn't so damned good at this. ;-) You know that you can use Perl inside PostgreS via plperl? HTH, Markus ---(end of broadcast)--- TIP 3: Have you checked o

Re: [PERFORM] Best COPY Performance

index updates cause both CPU load, and random disk access (which is slow by nature). HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org -

Re: [PERFORM] Index on two columns not used

t versions of the same tuple), and so an index change is not necessary when both versions fit on the same page. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ff

Re: [PERFORM] Index on two columns not used

a new row version in the same heap page, > the index must be updated anyway. AFAICS only, when the index covers (directly or via function) a column that's actually changed. Changing columns the index does not depend on should not need any write access to that index. Correct me if I'm

Re: [PERFORM] Index on two columns not used

s). So it may be necessary to re-check the condition with the real data, using the lossy index for preselection. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www

Re: [PERFORM] Best COPY Performance

out making your perl program writing the COPY statement as text, and piping it into psql. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org --

Re: [PERFORM] VACUUM FULL ANALYZE on 8.1.4 is slower then on 8.0

n equal disks? And in the same areas of those disks? Some current disks tend to drop down their speed at the "end" of the LBA address space drastically. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development

Re: [PERFORM] Scrub one large table against another (vmstat output)

7; numbers to be queried per group (all 40 groups > are displayed on the same page) and so constant aggregates over the > entire table would be a nightmare. That sounds just like a case for GROUP BY and a materialized view. Markus -- Markus Schaber | Logical Tracking&Tracing Inte

Re: [PERFORM] Poor performance on very simple query ?

act, the SERIAL type does nothing but defining a sequence, and then use nextval('sequencename') as default. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Eur

Re: [PERFORM] Performance Optimization for Dummies 2 - the SQL

ing real row counts etc. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)

Re: [PERFORM] Performace Optimization for Dummies

ll think that using a PL in the backend might be more performant than having an external client, alone being the SPI interface more efficient compared to the network serialization for external applications. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. |

Re: [PERFORM] Performace Optimization for Dummies

p by", "order by", "distinct on" and hand-written functions and aggregates (like first() or best()) may help. You could combine all relevant columns into an user-defined compund type, then group by entity, and have a self-defined aggregate generate the accumulated tu

Re: [PERFORM] Unsubscribe

Hi, Uwcssa, uwcssa wrote: > > Please unsubscribe me! Thank you! Sorry, but we (the list members) are unable do that, we have no adminstrative power on the list. :-( > Also, it would be better to have a message foot saying how to unsubscribe. List unsubscribe information is contained in t

Re: [PERFORM] how to optimize postgres 8.1

if the query plan stays equal. Do you look up the definition of cost for Informix? Have you made shure that they're comparable? HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Eur

Re: [PERFORM] Performace Optimization for Dummies

ovacuum to kick in are configurable, see the docs. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org --

Re: [PERFORM] Performace Optimization for Dummies

the desired result? We're using this way for some 3rd-party databases we have to process in-house. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwar

Re: [PERFORM] Decreasing BLKSZ

Hi, Marc, Marc Morin wrote: >> I wonder whether there is a way to use table partitioning to >> make the insert pattern more localized? We'd need to know a >> lot more about your insertion patterns to guess how, though. > > We're doing partitioning as well. And is constraint exclusion set

Re: [PERFORM] Decreasing BLKSZ

e new multi-row INSERT to further improve performance, but I doubt that you're CPU bound. The only way to "really" get over the sync limit is to have (at least) the WAL on a battery backed ram / SSD media that has no "spinning disk" physical limit, or abandon crash safety by t

Re: [PERFORM] Multi-processor question

a running top to multi-cpu mode, pressing "1" again switches back to accumulation. -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org -

Re: [PERFORM] Confusion and Questions about blocks read

little, but I don't know whether it's worth the effort, given that bitmap indidex scans exist. Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.

Re: [PERFORM] Large tables (was: RAID 0 not as fast as

as the application which has more informations about semantics and algorithms. Everything else would need a crystal ball device :-) HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents

Re: [PERFORM] Large tables (was: RAID 0 not as fast as

do you have a concrete problem? In latter case, you could post your details here, and we'll see whether we can help. Btw, I'm not related with Bizgres in any way. :-) HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Softwar

Re: [PERFORM] Large tables (was: RAID 0 not as fast as

I hope that this lengthy mail is enlightening, if not, don't hesitate to ask. Thanks for your patience, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatent

Re: [PERFORM] Large tables (was: RAID 0 not as fast as

Hi, Luke, Luke Lonergan wrote: >> I thought that posix_fadvise() with POSIX_FADV_WILLNEED was exactly >> meant for this purpose? > > This is a good idea - I wasn't aware that this was possible. This possibility was the reason for me to propose it. :-) > We'll do some testing and see if it work

Re: [PERFORM] Large tables (was: RAID 0 not as fast as

Hi, Luke, Luke Lonergan wrote: >> Do you think that adding some posix_fadvise() calls to the backend to >> pre-fetch some blocks into the OS cache asynchroneously could improve >> that situation? > > Nope - this requires true multi-threading of the I/O, there need to be > multiple seek operation

Re: [PERFORM] Large tables (was: RAID 0 not as fast as

ily be #define'd out on platforms that don't support it. Combine this with the Linux Kernel I/O Scheduler patches (readahead improvements) that were discussed here in summer... Regards, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Developmen

Re: [PERFORM] High CPU Load

I don't have much more ideas what the problem could be. Can you try to do some profiling (e. G. with statement logging) to see what specific statements are the one that cause high cpu load? Are there other differences (besides the PostgreSQL version) between the two installations? (Kernel,

Re: [PERFORM] High CPU Load

ot broken or misconfigured, always reconnect? HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)-

Re: [PERFORM] Poor performance on seq scan

is one of the more debated points in the PostgreSQL way of MVCC implementation. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarep

Re: [PERFORM] Vacuums on large busy databases

at them as comments, and uses the defaults instead. You should make shure that you use "real" settings in your config. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Eur

Re: [PERFORM] High CPU Load

h kill performance. > with 2 Go RAM and good SCSI disks. For 2 Gigs of ram, you should cut down the number of concurrent backends. Does your machine go into swap? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight

Re: [PERFORM] sql-bench

command in their benchmarks. Realistic in-production workloads don't have so much create table commands, I think. Wondering, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org

Re: [PERFORM] database bloat,non removovable rows, slow query etc...

this behaviour? it'is lovely...:) > > Tom , can you explain why?... It is because the transaction IDs are global per cluster. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! ww

Re: [PERFORM] database bloat,non removovable rows, slow query etc...

ty might help you identify the > culprit(s). Another possibility might be an outstanding two-phase-commit transaction. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU!

Re: [PERFORM] Identifying bloated tables

with a bloat value <1 and a negative "wasted space" - is this due to the pseudo nature of them? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! ww

Re: [PERFORM] perf pb solved only after pg_dump and restore

for the whole > database) For 7.4, you'll need a script to do that (current versions have improved in this area). You might recycle the idea from the pgsql-sql list some days ago: http://archives.postgresql.org/pgsql-sql/2006-08/msg00184.php Simply use the meta tables to get a list of al

Re: [PERFORM] perf pb solved only after pg_dump and restore

nk that vacuum during working hours puts too much load on your server, there are options to tweak that, at least in 8.1. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.n

Re: [PERFORM] perf pb solved only after pg_dump and restore

Unfortunately, we didn't keep the logs of VACUUM ANALYZE, so I > can't be sure :/ AFAIK, the warning is also output on the psql command line. HTH, Markus [1] We once had an index that was about 100 times larger before REINDEX. -- Markus Schaber | Logical Tracking&Tracing Internatio

Re: [PERFORM] perf pb solved only after pg_dump and restore

em. It also might make sense to issue a CLUSTER instead (which combines the effects of VACUUM FULL, REINDEX and physically reordering the data). When the free_space_map is to low, VACUUM ANALYZE should have told you via a warning (at least, if your logging is set appropriately). HTH, Markus -- Markus

Re: [PERFORM] VACUUM FULL needed sometimes to prevent transaction

Hi, Jeff & all, Jeff Davis wrote: > (2) You have a long-running transaction that never completed for some > strange reason. I just asked myself whether a 2-phase-commit transaction that was prepared, but never committed, can block vacuuming and TID recycling. Markus -- Mark

Re: [PERFORM] PostgreSQL runs a query much slower than BDE and MySQL

debian with their multi-cluster multi-version script wrapper magic. Don't mind. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org -

Re: [PERFORM] PostgreSQL runs a query much slower than BDE and MySQL

elf, that I had installed the correct postgresql and psql versions, but accidentally connected to a different database installation due to strange environment and script settings... Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Softwa

Re: [PERFORM] Postgresql Performance on an HP DL385 and

ll, actually, the controller should not reorder over write barriers. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---

Re: [PERFORM] Slow access to PostgreSQL server

ewall or antivirus network filtering software. HTH, Marku -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [PERFORM] slow transfer speeds with PostgreSQL

rying to estimate future usage patterns). HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)-

Re: [PERFORM] Migrating data from DB2 to SQL Server

that. Thank you. It seems that you, by accident, hit the wrong list with your question. But, as you're here, why don't you migrate to PostgreSQL instead? Have a nice day, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development G

Re: [PERFORM] [BUGS] BUG #2567: High IOWAIT

settings might help you out. As far as I understand, this will only help for concurrent inserts by different clients, dealing throughput for latency. Please correct me if I'm wrong. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Deve

Re: [PERFORM] Beginner optimization questions, esp. regarding Tsearch2

ound writer, stats collector or autovacuum, depending on your version and configuration. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Re: [NOVICE] [PERFORM] 7.3.2 pg_restore very slow

g the kernel. As stated on the http://www.postgresql.org/docs/8.1/interactive/kernel-resources.html page, those values can be changed via sysctl or echoing values into /proc, under linux at least. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Sof

Re: [PERFORM] sub select performance due to seq scans

Hi, Scott and Hale, Scott Marlowe wrote: > Make sure analyze has been run and that the statistics are fairly > accurate. It might also help to increase the statistics_target on the column in question. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG

Re: [PERFORM] PostgreSQL scalability on Sun UltraSparc T1

se results for low concurrency situations). Different fsync method settings can also make a difference (I presume that syncing was enabled). HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against so

Re: [PERFORM] Performance with 2 AMD/Opteron 2.6Ghz and 8gig

'm used to with the Adaptec SCSI junk. Well, for sequential reading, you should be able to get double drive speed on a 2-disk mirror with a good controller, as it can balance the reads among the drives. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf.

Re: [PERFORM] Disk writes

[1] We once had such a problem because an ill-compiled kernel having USB verbose logging on... -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org --

Re: [PERFORM] loading increase into huge table with 50.000.000 records

Hi, Larry, Hi, Sven, Sven Geisler wrote: > You can increase wal_buffers, checkpoint_segments and checkpoint_timeout > much higher. You also should increase the free space map settings, it must be large enough to cope with your weekly bunch. Markus -- Markus Schaber | Logical Tr

Re: [PERFORM] BUG #2543: Performance delay acrros the same day

gh and/or don't have your FSM > setting high enough. Depending on the PostgreSQL version, it might also be that he suffers from index bloat. He might look into the manual pages about REINDEX for a description. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing Inter

Re: [PERFORM] RAID stripe size question

'll definitely fiddle with that in the coming > tests as well. How many parallel transactions do you have? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU!

Re: [PERFORM] RAID stripe size question

e system.) HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Kill a session

is is for plpgsql, pljava and plpython? HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)

Re: [PERFORM] Update INSERT RULE while running for Partitioning

the affected-row count > on the last rule which gives Hibernate problems. This could be considered a PostgreSQL bug - maybe you should discuss this on the appropriate list (general, hackers)? HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. |

Re: [PERFORM] need vacuum after insert/truncate/insert?

ble, to have current statistics. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)-

Re: [PERFORM] getting better performance

dropping and recreation. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)---

Re: [PERFORM] Is postgresql ca do the job for software deployed in

PostgreSQL) we use the one schema per customer paradigm quite successfully. > My $0.02 (not worth what it was) Oh, I think the're at least $0.03 cents worth. :-) Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight again

Re: [PERFORM] Is postgresql ca do the job for software deployed in

d from listing all schemas (or even all databases in the same user), regardless whether he/she has access rights. But it is not always acceptable that a customer knows which other customers one has. This forces the use of the "one cluster per customer" paradigm. Thanks, Markus -- Markus S

Re: [PERFORM] Index Being Ignored?

dx ON purchase_order_items (expected_quantity) WHERE expected_quantity > 0; HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---

Re: [PERFORM] Help tuning autovacuum - seeing lots of relationbloat

s is pretty clearly in the 1% > it can't handle. Maybe your free space map is configured to small, can you watch out for log messages telling to increase it? HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight ag

Re: [PERFORM] Help tuning autovacuum - seeing lots of relationbloat

when leaving it open while having lunch... Some older JDBC driver versions had the bug that they always had an open transaction, thus an application server having some pooled connections lingering around could block vacuum forever. Markus -- Markus Schaber | Logical Tracking&Tracing Inte

Re: [PERFORM] SAN performance mystery

Hi, Tim, Seems I sent my message to fast, cut in middle of a sencence: Markus Schaber wrote: >> A pg_dump/pg_restore cycle reduced the total >> database size from 81G to 36G. > If you still have the original database around, ... can you check wether VACUUM FULL and REINDEX a

Re: [PERFORM] SAN performance mystery

e, and increase the WAL size / checkpoint segments. When most of the restore time was spent in index creation, increase the sort mem / maintainance work mem settings. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fig

Re: [PERFORM] Some queries starting to hang

le. Most of the cases when we had database bloat despite running autovacuum, it was due to a low free_space_map setting. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosof

Re: [PERFORM] scaling up postgres

2 G. This sounds like you want to have one postgresql backend per apache frontend. Did you try running pgpool on the Apache machine, and have only a few (hundred) connections to the backend? Maybe http://en.wikipedia.org/wiki/Memcached could be helpful, too. Markus -- Markus Schaber | Lo

Re: [PERFORM] scaling up postgres

atabase is only 58M it's a read only DB and will lasts only for a > month. I guess it is a simple table with a single PK (some subscription numer) - no joins or other things. For this cases, a special non-RDBMS like MySQL, SQLite, or even some hancrafted thingy may give you better resu

Re: [PERFORM] lowering priority automatically at connection

a, most Datasource implementations (e. G. the JBoss one) allow to specify SQL statements that are executed on connection init. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www

Re: [PERFORM] Getting even more insert performance (250m+rows/day)

and input file to different spindles won't help you much. HTH Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---

Re: [PERFORM] How can I make this query faster (resend)

ctions, and then give them at least 30 megs of RAM each. This should also cut down the connection creation overhead. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! ww

Re: [PERFORM] Lot'sa joins - performance tip-up, please?

Hi, Mario, Mario Splivalo wrote: > This helps also. I don't get sequential scans any more. I'd like a tip > on how to set 'enable_nestloop = off' trough JDBC? statement.execute("SET enable_nestloop TO off"); should do. HTH, Markus -- Markus Schaber | Logic

Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal

aps a better solution would be to cache the > result of the estimator function. Sophisticated estimator functions are free to use the pg_statistics views for their row count estimation. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Dev

Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal

E. G. for generate_series we return ($2-$1)/$3, and for some functions even constant estimates will be good enough. > - please execute me and store my results in a temporary storage, > count the rows returned, and plan the outer query accordingly That's an interesting idea.

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

Hi, Bruce, Markus Schaber wrote: >>>It does not find as much liers as the script above, but it is less >>Why does it find fewer liers? > > It won't find liers that have a small "lie-queue-length" so their > internal buffers get full so they have to bloc

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

), but not syncs/write rate. Both of them can be captured by the other script, but not by my tool. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatent

Re: [PERFORM] [GENERAL] Arguments Pro/Contra Software Raid

SB/FireWire to IDE case, and an older linux cryptoloop implementations, IIRC). If you're interested, I can dig for the C source... HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software

Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal

e estimator functions have an implicit return parameter of int8. Parameters may be NULL when they are not known at query planning time. What do you think about this idea? The same scheme could be used to add a CPUCOST_ESTIMATOR to expensive functions. HTH, Markus -- Markus Schaber | Logical Track

Re: [PERFORM] extremely slow when execute select/delete for certain

ur indices using REINDEX command. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)-

Re: [PERFORM] Why so slow?

Additonally, the "free_space_map" setting has to be high enough, it has to cover enough space to put in all pages that get dead rows between two vacuum runs. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight

[PERFORM] Arrays and index scan

ter".flatten_array = streets.link_id) (4 rows) Currently, we're planning to use the array flattening approach, but are there any plans to enhance the query planner for the direct ARRAY approach? Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. I

Re: [PERFORM] how unsafe (or worst scenarios) when setting fsync

g with commit_delay and commit_siblins may improve your situation. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of

Re: [PERFORM] Recovery will take 10 hours

will try to access a not-completely-restored wal file. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---

Re: [PERFORM] Introducing a new linux readahead framework

hink that this is an easy, understandable and useful interpretation of posix_fadvise() hints. Are there any rough estimates when this will get into mainline kernel (if you intend to submit)? Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. |

Re: [PERFORM] Quick Performance Poll

to a read-only table for now. Are they capable to index custom datatypes like the PostGIS geometries that use the GIST mechanism? This could probably speed up our Geo Databases for Map rendering, containing static data that is updated approx. 2 times per year. Markus -- Markus Schaber | Logical Tr

Re: [PERFORM] Inserts optimization?

oject-du-jour are > "dumbed down" to the mysql featureset. (And not just mysql, but > mysql-lowest-common-factors, which means myisam etc) Well, most of those projects don't need a database, they need a bunch of tables and a lock. Heck, they even use client-side SELECT-loops

Re: [PERFORM] Inserts optimization?

g extra delays. Well, if you read the documentation, you will see that it will only wait if there are at least commit_siblings other transactions active. So when Bacula serializes access, there will be no delays, as there is only a single transaction alive. HTH Markus -- Markus Schaber | Lo

Re: [PERFORM] merge>hash>loop

s a crude way of partially compensating for this basic problem. I totall agree with this, it's just what we did here from time to time. :-) Hmm, how does effective_cach_size correspond with it? Shouldn't a high effective_cache_size have a similar effect? Thanks, Markus -- Markus Schaber |

Re: [PERFORM] Inserts optimization?

at has successfully finished a commit can be shure their data is on the platters.[1] HTH, Markus [1] As long as the platters don't lie, but that's another subject. -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against so

Re: [PERFORM] pgmemcache

it infrastructure for them. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- T

Re: [PERFORM] Better index stategy for many fields with few values

indices on different disks / raid arrays, to parallelize writes. Btw, I guess you have multiple, concurrent users? Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii

  1   2   >