Re: [PERFORM] Defining performance.

due to unique key violations, the dead rows won't be a problem. 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

Re: [PERFORM] Regex performance issue

-1 | 0 | 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

Re: [PERFORM] Regex performance issue

e exact algorithm for adding the rows, but I'm pretty sure 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 XXXXY, we add another row with id A and prefix (Y+1). -- Heikki Linnakangas EnterpriseDB

Re: [PERFORM] single transaction vs multiple transactions

able which I can use in 'DELETE FROM x 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: Hav

Re: [PERFORM] single transaction vs multiple transactions

no indexes 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: H

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 Postg

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

ad 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

cluding the indexes, people might have more ideas... -- 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

ot 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

Why the big 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

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

postgresql server? I'd say it's more 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 runnin

Re: [PERFORM] quad or dual core Intel CPUs

eading the results backwards. PostgreSQL throughput increased, not decreased, by the upgrade. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by d

Re: [PERFORM] PostgreSQL 8.2.3 VACUUM Timings/Performance

ime like vacuum 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

re hasn't been a pressing reason to remove it either. -- 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 i

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

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] function performance vs in-line sql

ion, the manual sql query and the schema, please? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [PERFORM] Question about PGSQL functions

procedure_code = '' THEN source.procedure_code ELSE NULL END;) from source where summary_table.source_id=source.source_id; -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/rea

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, t

Re: [PERFORM] Autocommit in libpq

. -- 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] Postgres batch write very slow - what to do

h before waiting for responses. -- 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] 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.

Re: [PERFORM] Vacuum full is slow

e manual suggests dropping all indexes before running vacuum full, 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 ?

with your 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

vely eliminate 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 colu

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

u don't need 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

in the same transaction (or TRUNCATE) as you load 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

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

ce 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

Re: [PERFORM] Fragmentation of WAL files

ystems might be smarter than others in placing the fragments. There's 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 ---

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

, but in reality it's in cache and the index 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.enterp

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&#x

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

er transaction 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?

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 o

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

currently 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. --

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

's not recorded in the FSM. When vacuum runs, 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)

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

given table... See pgstatindex, in the same 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.enterpr

Re: [PERFORM] Kernel cache vs shared_buffers

indexes 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 Ente

Re: [PERFORM] Kernel cache vs shared_buffers

s. My 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 ---(e

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

L archiving isn't enabled. -- 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

ures, where 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 Linnakan

Re: [PERFORM] choosing fillfactor

dated 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.e

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

with the table definitions and indexes of all tables involved in the query. I'm 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 Linn

Re: [PERFORM] max_fsm_pages, shared_buffers and checkpoint_segments

troller with battery backed up 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 do

Re: [PERFORM] PITR performance costs

is enabled. -- 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 --

Re: [PERFORM] dbt2 NOTPM numbers

ta drives, and I'm getting reasonable 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

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

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, ther

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

ser action, and allows updates. -- 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

onnections 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 co

Re: [PERFORM] Variable (degrading) performance

nts, as well as a bunch of other 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

ng enough. Long 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 y

Re: [PERFORM] Performance Testing Utility

pose since 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

nt.html#GUC-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 broa

Re: [PERFORM] Short row header

e header fields 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. -- Hei

Re: [PERFORM] Volunteer to build a configuration tool

. In a nutshell, we rely on the OS to not only do caching for us, but I/O 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 t

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

e in index 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

Re: [PERFORM] Database-wide VACUUM ANALYZE

al, before and after 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

what the limit would 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 yo

Re: [PERFORM] PREPARE and stuff

ed plan. How would 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 Lin

Re: [PERFORM] update query taking too long

rom running out 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://

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

ata drives 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

Re: [PERFORM] improvement suggestions for performance design

onality 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

Re: [PERFORM] improvement suggestions for performance design

ratio that runs for hours. Though if you choose 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)--

Re: [PERFORM] Advice about how to delete

M user_groups where group_id = 10) ); Or maybe 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

ishes-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

Re: [PERFORM] PostgreSQL publishes first real benchmark

rver using 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? --

Re: [PERFORM] one column from huge view

oesn't return any columns from 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 p

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

Re: [PERFORM] importance of fast disks with pg

g at 100%, getting faster disks doesn't help anymore. 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 appli

Re: [PERFORM] ionice to make vacuum friendier?

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

Re: [PERFORM] How to use a trigger to write rows to a remote server

it brakes just after inserting the row to the other db, but before committing. Or if the insert on the other server succeeds, but the local transaction aborts. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)---

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

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

and the new value inserted 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? -- He

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

ssary indexes in recent 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 E

Re: [PERFORM] Extreme slow select query 8.2.4

ified timeout, it's automatically aborted and an error 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 cho

Re: [PERFORM] Update table performance

many of the rows deleted in > earlier updates. Only if you vacuum between the updates. -- 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

ue is never handed back from the index; the indexed values 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. --

Re: [PERFORM] Performance on writable views

tch an record to be updated (ie. to get OLD.*). There is some overhead in rewriting 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 ca

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

#x27;d guess that the the planner doesn't know which >> partition holds the latest time so it has to read them all. > > Agree. But why it not uses indexes when it reading them? The planner isn't smart enough to push the "ORDER BY ... LIMIT ..." below the appen

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

N -r1.52.2.4 -r1.52.2.5 > --- cdb-pg/src/backend/optimizer/util/pathnode.c5 Aug 2007 23:06:44 > -1.52.2.4 > +++ cdb-pg/src/backend/optimizer/util/pathnode.c10 Aug 2007 03:41:15 > -1.52.2.5 > @@ -1563,7 +1563

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

If the user_bookmark table is not clustered by uid, I'm 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.e

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 co

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

o the DRBD device. Using a RAID controller with a battery-backed up cache in both servers should help, with and without DRBD. You might find that the difference between local and shared partition just gets bigger, but you should get better numbers. In 8.3, you could turn synchronous_commit=off, if you

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

ough the OS should already doing read ahead for us. How 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...

e autovacuum_cost_delay. Or decrease it if it can't keep up with the updates. BTW, you didn't mention which version of PostgreSQL you're using. There's been some performance enhancements to VACUUM in 8.2, as well as autovacuum changes. You might consi

Re: [PERFORM] DELETE queries slow down

AIN ANALYZE output of the DELETE? It might be choosing a bad plan after the table grows. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropria

Re: [PERFORM] Low CPU Usage

to arrive from the disk. That does iostat say about disk utilization on both servers? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropr

Re: [PERFORM] Linux mis-reporting memory

gres is this? In 8.3, a scan like that really won't suck it all into the shared buffer cache. For seq scans on tables larger than shared_buffers/4, it switches to the bulk read strategy, using only a few buffers, and choosing the starting point with the scan synchronization facilit

Re: [PERFORM] select count(*) performance (vacuum did not help)

es (in around 80 days) That bloated your table, so that there's still a lot of empty pages in it. VACUUM FULL should bring it back to a reasonable size. Regular normal non-FULL VACUUMs should keep it in shape after that. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---

Re: [PERFORM] select count(*) performance (vacuum did not help)

ck and wait until it's finished. > or in a different way: > > if i do a full vacuum to that table only, will the database still serve > data from the other tables at a normal speed? Yes. The extra I/O load vacuum full generates while it's running might disrupt other

Re: [PERFORM] Difference in query plan when using = or > in where clause

count(*) from View_A WHERE tradedate BETWEEN '20070801' and > '20070901'; > The query plan is: > ... In short, the planner estimates that "tradedate BETWEEN '20070801' and '20070901'" matches more rows than &

Re: [PERFORM] Non-blocking vacuum full

ning in serializable mode shouldn't throw a serialization error when it tries to update an old, moved row version, but follow the ctid pointer instead. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)---

  1   2   3   4   >