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

Markus Schaber wrote: I just asked myself whether a 2-phase-commit transaction that was prepared, but never committed, can block vacuuming and TID recycling. Yes. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast

Re: [PERFORM] Poor performance on seq scan

to implement index-only scans. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your

Re: [PERFORM] Poor performance on seq scan

heap tuple to see if it matches %Mug%. It's just going to do it in index order, which is slower than a seq scan. BTW: in addition to setting enable_seqscan=false, you probably have to add a dummy where-clause like name '' to force the index scan. -- Heikki Linnakangas EnterpriseDB http

Re: [PERFORM] Performace Optimization for Dummies

bound? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] simple case using index on windows but not on linux

differences in configuration. You can increase the max shared memory size if you have root access. See http://www.postgresql.org/docs/8.1/interactive/kernel-resources.html#SYSVIPC-PARAMETERS Scroll down for Linux-specific instructions. -- Heikki Linnakangas EnterpriseDB http

Re: [PERFORM] Simple join optimized badly?

them automatically. BTW, in DB2 you can declare a table as volatile, which means that the cardinality of the table varies greatly. The planner favors index scans on volatile tables. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end

Re: [PERFORM] Hints proposal

remember correctly, you could even define CHECK constraints that weren't actually checked at run-time, but were used by the planner. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill

Re: [PERFORM] Optimization of this SQL sentence

You could try rewriting the query like this: SELECT MAX(idcomment) FROM comment c WHERE idstatus=3 AND ctype=1 AND EXISTS (SELECT 1 FROM ficha vf WHERE idestado IN ('3', '4') AND vf.idficha = c.idfile); The planner can then try a backward scan on the comment_pkey index, which should be

Re: [PERFORM] Index on two columns not used

://archives.postgresql.org/pgsql-performance/2006-09/msg00080.php. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [PERFORM] Jdbc/postgres performance

prepare, and the query will be re-planned every time you execute it with the real values of the parameters. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [PERFORM] Index on two columns not used

, is that good enough? It's doesn't sound too bad, considering that it returned almost 4 rows. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Index on two columns not used

analyze of the query instead of the definition of the view. The access plan might look very different. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [PERFORM] big transaction slows down over time - but disk seems

, and a command within the transaction is doing a seq scan that has to scan through all of them. Or something like that. It's hard to tell without more details. Calling stored procedures repeatedly shouldn't cause a slowdown over time. -- Heikki Linnakangas EnterpriseDB http

Re: [PERFORM] Yet another question on LIMIT performance :/

Hannes Dorbath wrote: Though it should only have to join a few rows it seems to scan all rows. What makes you think that's the case? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain

Re: [PERFORM] Postgres scalability and performance on windows

see how much memory is used for caching. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Defining performance.

. The space will be reclaimed by vacuum. In general, it's normal that there's some dead rows in the database. As long as you vacuum regularly, the database size should eventually reach a steady-state where it doesn't grow anymore, unless the real live dataset size increases. -- Heikki Linnakangas

Re: [PERFORM] Regex performance issue

| 0035570 Now you can do SELECT * FROM destlist WHERE ? = prefix ORDER BY prefix LIMIT 1. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http

Re: [PERFORM] Regex performance issue

it can be automated... The basic idea is that when there's a row with id A and prefix , and another row with id B and prefix Y, we add another row with id A and prefix (Y+1). -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end

Re: [PERFORM] single transaction vs multiple transactions

WHERE y IN (SELECT z FROM tmp)'. I think that's exactly what you should do. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http

Re: [PERFORM] single transaction vs multiple transactions

should be quite inexpensive if you make sure you don't do it one record at a time. Use the COPY command or batched inserts instead. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked

Re: [PERFORM] Planner statistics, correlations

Tobias Brox wrote: Maybe it would help to partitionate the table every year? I thought about partitioning the table by state, putting rows with state=4 into one partition, and all others to another partition. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com

Re: [PERFORM] Planner statistics, correlations

Tobias Brox wrote: [Heikki Linnakangas - Fri at 10:41:34AM +] I thought about partitioning the table by state, putting rows with state=4 into one partition, and all others to another partition. That sounds like a good idea - but wouldn't that be costly when changing state? In PostgreSQL

Re: [PERFORM] Caching in PostgreSQL

? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [PERFORM] Caching in PostgreSQL

of trying to peek into PostgreSQL internals. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Configuration Advice

... -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] [OT] Very strange postgresql behaviour

of rules that I don't paste as matter of length. Is there any SELECT rules by chance that might explain this? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] limit + order by is slow if no rows in result set

difference in performance? Please run EXPLAIN ANALYZE on both queries, and send back the results. Also, what indexes are there on the tables involved? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7

Re: [PERFORM] many instances or many databases or many users?

a question of isolation and administration than performance. For example, do you want to be able to do filesystem-level backups and restores one database at a time? Do you need to shut down one database while keeping the rest of them running? -- Heikki Linnakangas EnterpriseDB http

Re: [PERFORM] PostgreSQL 8.2.3 VACUUM Timings/Performance

full. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [PERFORM] PostgreSQL 8.2.3 VACUUM Timings/Performance

Aidan Van Dyk wrote: * Heikki Linnakangas [EMAIL PROTECTED] [070305 09:46]: In fact, getting rid of vacuum full, or changing it to work like cluster, has been proposed in the past. The use case really is pretty narrow; cluster is a lot faster if there's a lot of unused space in the table

Re: [PERFORM] Automated test-suite for Postgres

Neelam Goyal wrote: Is anyone aware of some test-suite for Postgresql? What do you want to test? PostgreSQL itself or some application using it? Do you want to do performance testing or functional regression testing, perhaps? -- Heikki Linnakangas EnterpriseDB http

Re: [PERFORM] Postgres batch write very slow - what to do

the Populating a Database chapter in the manual: http://www.postgresql.org/docs/8.2/interactive/populate.html -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner

Re: [PERFORM] Determine dead tuples size

Alexey Romanchuk wrote: thanks, i install contribs and try to analyze result of pgstattuple function and found it strange. Try SELECT * FROM pgstattuple('foo'), that'll tell you what the columns are. Take a look at README.pgstattuple as well for more details. -- Heikki Linnakangas

Re: [PERFORM] Vacuum full is slow

, and recreating them afterwards. That's worth trying. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Optimization postgresql 8.1.4 FC 6 X64 ?

real database. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Re: [PERFORM] SCSI vs SATA

the delay that causes. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes

Re: [PERFORM] TPC-H Scaling Factors X PostgreSQL Cluster Command

to cluster at all. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] TPC-H Scaling Factors X PostgreSQL Cluster Command

the data, the COPY will skip writing WAL which can give a nice speedup. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http

Re: [PERFORM] What`s wrong with JFS configuration?

of that test is bound by the speed your drives can flush WAL commit records to disk. I wouldn't expect the filesystem to make a big difference anyway, but you'll see.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast

Re: [PERFORM] Fragmentation of WAL files

a tool for Linux in the e2fsprogs package called filefrag that shows the fragmentation of a file, but I've never used it myself. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked

Re: [PERFORM] Index not being used in sorting of simple table

scan is faster because it doesn't need to sort. Have you set your effective_cache_size properly? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [PERFORM] How to Find Cause of Long Vacuum Times - NOOB Question

. If the increase in vacuum time is indeed because of index fragmentation, upgrading to 8.2 might help. Since 8.2, we vacuum indexes in physical order, which speeds it up significantly, especially on fragmented indexes. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com

Re: [PERFORM] Best OS for Postgres 8.2

[EMAIL PROTECTED] wrote: if you don't journal your data then you avoid the problems above, but in a crash you may find that you lost data, even though the filesystem is 'intact' according to fsck. PostgreSQL itself journals it's data to the WAL, so that shouldn't happen. -- Heikki

Re: [PERFORM] truncate a table instead of vaccum full when count(*) is 0

running in serializable mode. It sounds like it's not a problem in your scenario, but it's hard to say for sure without seeing the application. Running vacuum more often is probably a simpler and better solution, anyway. Which version of PostgreSQL is this? -- Heikki Linnakangas

Re: [PERFORM] truncate a table instead of vaccum full when count(*) is 0

Pomarede Nicolas wrote: On Tue, 8 May 2007, Heikki Linnakangas wrote: Pomarede Nicolas wrote: But for the data (dead rows), even running a vacuum analyze every day is not enough, and doesn't truncate some empty pages at the end, so the data size remains in the order of 200-300 MB, when only

Re: [PERFORM] truncate a table instead of vaccum full when count(*) is 0

not removable (which is normal). Oh, I see. I know you don't want to upgrade, but that was changed in 8.2. Vacuum now ignores concurrent vacuums in the oldest xid calculation, so the long-running vacuum won't stop the vacuum on the spool table from removing dead rows. -- Heikki Linnakangas

Re: [PERFORM] truncate a table instead of vaccum full when count(*) is 0

, it physically removes tuples from the table and frees the space occupied by them. At the end it updates the FSM. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map

Re: [PERFORM] estimating the need for VACUUM FULL and REINDEX

contrib-module. The number you're looking for is avg_leaf_density. REINDEX will bring that to 90% (with default fill factor), so if it's much lower than that REINDEX will help. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast

Re: [PERFORM] Kernel cache vs shared_buffers

on the larger tables and would seldom, if ever, do sequential scans. A common rule of thumb people quote here is to set shared_buffers to 1/4 of available RAM, and leave the rest for OS cache. That's probably a good configuration to start with. -- Heikki Linnakangas EnterpriseDB http

Re: [PERFORM] Kernel cache vs shared_buffers

current rule of thumb on Windows: set shared_buffers to minimum * 2 Adjust effective_cache_size to the number given as system cache within the task manager. Why? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast

Re: [PERFORM] Many to many join seems slow?

. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [PERFORM] WAL log performance/efficiency question

. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] WAL log performance/efficiency question

the header used to be padded up to 32 bytes, and now it's only 24 bytes. For character fields, including CHAR(100) like you have, we also store a 4 bytes length header per field. That's been reduced to 1 byte for string shorter than 127 bytes in 8.3. -- Heikki Linnakangas EnterpriseDB http

Re: [PERFORM] choosing fillfactor

on average about 5 times but for some of the columns with indexes don't change after insertion ever. thanks for any advice It's the number of times the row is updated, regardless of which columns are changed. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com

Re: [PERFORM] any way to get rid of Bitmap Heap Scan recheck?

pondering encoding the tag name to int or bytea field(s) and joining on them but that's kinda ugly. I doubt that helps, but it's hard to say without seeing the schema. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast

Re: [PERFORM] max_fsm_pages, shared_buffers and checkpoint_segments

cache. Or wait until release 8.3, which should fix that issue. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http

Re: [PERFORM] PITR performance costs

. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

or not. In the interim, I did an 'initdb' to another location on the same box and then copied those values into the config file. That's cool to do, I assume? Yeah, that's ok. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast

Re: [PERFORM] dbt2 NOTPM numbers

results with ~100 warehouses, at ~1200 noTPM. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about

Re: [PERFORM] dbt2 NOTPM numbers

Markus Schiltknecht wrote: Hi, Heikki Linnakangas wrote: There's clearly something wrong. The response times are ridiculously high, they should be 5 seconds (except for stock level transaction) to pass a TPC-C test. I wonder if you built any indexes at all? Hm.. according to the output/5

Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x

Gregory Stark wrote: Those plans look like they have a lot of casts to text in them. How have you defined your indexes? Are your id columns really text? And did you use the same encoding and locale? Text operations on multibyte encodings are much more expensive. -- Heikki Linnakangas

Re: [PERFORM] dbt2 NOTPM numbers

Markus Schiltknecht wrote: Hi, Heikki Linnakangas wrote: I still suspect there's something wrong with plans, I doubt you can get that bad performance unless it's doing something really stupid. Agreed, but I'm still looking for that really stupid thing... AFAICT, there are really

Re: [PERFORM] Thousands of tables versus on table?

. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate

Re: [PERFORM] pg_statistic doesnt contain details for specific table

Nimesh Satam wrote: While monitioring we noticed that there are no details in the pg_statistics for a particular table. Can you let us know what might be the reason? Also what steps can be taken care for adding the statistics? Have you ANALYZEd the table? -- Heikki Linnakangas

Re: [PERFORM] dbt2 NOTPM numbers

with ~100 warehouses on somewhat similar hardware. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL

Re: [PERFORM] Variable (degrading) performance

improvements. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org

Re: [PERFORM] Variable (degrading) performance

enough is probably hours, not minutes or seconds. As I said earlier, checkpoints and vacuum are a major source of variability. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our

Re: [PERFORM] Performance Testing Utility

the optimal configuration depends on your application, and pgbench is likely nothing like your application. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Regarding Timezone

-- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] Performance query about large tables, lots of concurrent access

have long nights ;-). On a serious note, the index vacuum improvements in 8.2 might help you to cut that down. You seem to be happy with your setup, but I thought I'd mention it.. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast

Re: [PERFORM] Short row header

more complex. (even if this makes the tuples non-updateable, as long as they can be deleted, it would be OK for this type of tables). That would save another 6 bytes per tuple (ctid field), but we generally stay away from things that impose limitations like that. -- Heikki Linnakangas

Re: [PERFORM] Volunteer to build a configuration tool

scheduling and readahead as well. That saves us a lot of code, and the OS is in a better position to do that as well, because it knows the I/O hardware and disk layout so that it can issue the I/O requests in the most efficient way. -- Heikki Linnakangas EnterpriseDB http

Re: [PERFORM] Performance query about large tables, lots of concurrent access

page order, which in worst case means random I/O, and we used to do an extra scan of all index pages to collect empty ones. Now it's all done as a single sequential pass. Or, is the database less sensitive performance-wise to delayed VACUUM commands? No. -- Heikki Linnakangas EnterpriseDB

Re: [PERFORM] Database-wide VACUUM ANALYZE

upgrade? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [PERFORM] Very long SQL strings

be. The most efficient way to do bulk inserts is to stream the data with COPY. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose

Re: [PERFORM] PREPARE and stuff

that be different from the current PREPARE/EXECUTE? Do you mean you could PREPARE in one connection, and EXECUTE in another? If you're using persistent connections, it wouldn't be any faster than doing a PREPARE once in each connection. -- Heikki Linnakangas EnterpriseDB http

Re: [PERFORM] update query taking too long

of disk space on the filesystem WAL is located in. Note that unlike on DB2, the size of your transactions isn't limited by the amount of transaction log you keep around; this is all about performance. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com

Re: [PERFORM] Filesystem Direct I/O and WAL sync option

as well to get consistency, because fsync=off disables checkpoint fsyncs of the data files as well. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map

Re: [PERFORM] improvement suggestions for performance design

or performance reasons. It will run on a sparc machine with solaris 10 and perhaps 4-6 processors, as many GB of RAM as necessary and SCSI disks ( perhaps in raid 0 ). -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast

Re: [PERFORM] improvement suggestions for performance design

to have just one table per attribute type, each table might be conveniently small by nature, so that no partitioning is required. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched

Re: [PERFORM] Advice about how to delete

you could just leave the orphans in the table, and delete them later in batch? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] PostgreSQL publishes first real benchmark

-first-real-benchmark-17470 That's really exciting news! I'm sure you spent a lot of time tweaking the settings, so let me ask you something topical: How did you end up with the bgwriter settings you used? Did you experiment with different values? How much difference did it make? -- Heikki

Re: [PERFORM] PostgreSQL publishes first real benchmark

Sun's Last Agent Logging Optimization; the 1PC database transactions and transaction log records are written to the database in a single transaction. Did you perhaps use 2PC at first, but didn't bother to change the config after switching to the last agent optimization? -- Heikki Linnakangas

Re: [PERFORM] one column from huge view

the outer relation. Also, if the calculation contains immutable functions, it's not skipped. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate

Re: [PERFORM] one column from huge view

Michael Fuhr wrote: On Thu, Jul 12, 2007 at 09:50:42AM +0100, Heikki Linnakangas wrote: Marcin Stępnicki wrote: Let's say I've got a view with 100 columns and 1mln rows; some of them are calculated on the fly. For some reason I want only one column from this view: select col1 from huge_view

Re: [PERFORM] importance of fast disks with pg

. At that point you need to get more CPU power. Here's the algorithm for increasing application throughput: while throughput is not high enough { identify bottleneck resolve bottleneck, by faster/more hardware, or by optimizing application } -- Heikki Linnakangas EnterpriseDB http

Re: [PERFORM] ionice to make vacuum friendier?

? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings

Re: [PERFORM] insert vs select into performance

Adriaan van Os wrote: So, how does one (temporarily) disable WAL logging ? Or, for example, disable WAL logging for a temporary table ? Operations on temporary tables are never WAL logged. Operations on other tables are, and there's no way to disable it. -- Heikki Linnakangas EnterpriseDB

Re: [PERFORM] Performance problems with large telemetric datasets on 7.4.2

afterwards, which should not be an issue with a btree, but do you guys know something more about it, sorry I'm really good in SQL but in Postgre I'm still a beginner. I don't remember a bug like that. Where did you read that from? -- Heikki Linnakangas EnterpriseDB http

Re: [PERFORM] Performance problems with large telemetric datasets on 7.4.2

versions under unusual access patterns, like if you delete all but a few index tuples from each index page, but it's rare in practice. And it's not unbounded growth like in = 7.3. In any case, the indexes won't become corrupt. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com

Re: [PERFORM] Extreme slow select query 8.2.4

is given. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [PERFORM] Update table performance

. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Bitmap Index Scan optimization opportunity

are only used to satisfy index conditions, not filters. It's been discussed before (see http://archives.postgresql.org/pgsql-performance/2006-09/msg00080.php), but it's not easy to implement so no one's done it yet. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com

Re: [PERFORM] Performance on writable views

the query, but it shouldn't be significantly slower than issuing the statements behind the view directly. I wouldn't worry about it, unless you have concrete evidence that it's causing problems. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end

Re: [PERFORM] partitioned table and ORDER BY indexed_field DESC LIMIT 1

; + +return pathnode; } /* On 8/24/07 3:38 AM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Anton wrote: =# explain SELECT * FROM n_traf ORDER BY date_time DESC LIMIT 1; QUERY PLAN

Re: [PERFORM] Performance problem with table containing a lot of text (blog)

surprised the planner didn't choose a bitmap index scan. Which version of PostgreSQL is this? PS. EXPLAIN ANALYZE is much more helpful than plain EXPLAIN. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP

Re: [PERFORM] Performance problem with table containing a lot of text (blog)

Kari Lavikka wrote: On Wed, 29 Aug 2007, Heikki Linnakangas wrote: The idea of being able to set the toast threshold per column was discussed during 8.3 development, but no patch was produced IIRC. We might do that in the future. If you're willing to compile from source, you can lower

Re: [PERFORM] DRBD and Postgres: how to improve the perfomance?

numbers. In 8.3, you could turn synchronous_commit=off, if you can accept the loss of recently committed transactions in case of a crash. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill

Re: [PERFORM] random_page_costs - are defaults of 4.0 realistic for SCSI RAID 1

efficient it is is another question. posix_fadvise(SEQUENTIAL) could be used to give a hint on that as well. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [PERFORM] More Vacuum questions...

version of PostgreSQL you're using. There's been some performance enhancements to VACUUM in 8.2, as well as autovacuum changes. You might consider upgrading if you're not on 8.2 already. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast

  1   2   3   >