Re: [PERFORM] partitioning and locking problems
On Tue, 2006-02-07 at 18:59 -0600, Jim C. Nasby wrote: > I'm honestly somewhat surprised someone hasn't run into this problem > with partitioning yet; or maybe everyone who needs to do long > transactions just shoves those off to slony slaves... All DDL takes locks, on all DBMS. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Default autovacuum settings too conservative
Hi, Christopher, Christopher Browne wrote: > Right. And part of the trouble is that you lose certainty that you > have covered off transaction wraparound. Yes. Vacuum (full) serve at least four purposes: - TID wraparound prevention - obsolete row removal - table compaction - giving space back to the OS by truncating files While the first one needs full table sweeps, the others don't. And from my personal experience, at least the obsolete row removal is needed much more frequently than TID wraparound prevention. >>When tables are tracked individually for wraparound, the longest >>transaction required for vacuuming will be one to vacuum one >>table. With delete-map and other functions, the time for that >>transaction may be reduced. Partial vacuum of large tables is an >>option, but again requires some real smarts in the autovac code to >>track wraparound issues. > > Unfortunately, "delete-map" *doesn't* help you with the wraparound > problem. The point of the "delete map" or "vacuum space map" is to > allow the VACUUM to only touch the pages known to need vacuuming. > > At some point, you still need to walk through the whole table (touched > parts and untouched) in order to make sure that the old tuples are > frozen. Preventing transaction ID wraparound needs a guaranteed full table sweep during a vacuum run, but not necessarily in a single transaction. It should be possible to divide this full table sweep into smaller chunks, each of them in its own transaction. It will certainly be necessary to block e. G. simultaneous VACUUMs, CLUSTERs or other maintainance commands for the whole VACUUM run, but normal SELECT, INSERT and UPDATE statement should be able to interleave with the VACUUM transaction. 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] optimizing away join when querying view
Postgres doesn't seem to optimize away unnecessary joins in a view
definition when the view is queried in such a way that the join need not
be executed. In the example below, I define two tables, foo and bar,
with a foreign key on bar referencing foo, and a view on the natural
join of the tables. The tables are defined so that the relationship
from bar to foo is allowed to be many to one, with the column of bar
referencing foo (column a) set NOT NULL, so that there must be exactly
one foo record for every bar record. I then EXPLAIN selecting the "b"
column from bar, through the view and from bar directly. The tables
have been ANALYZEd but have no data. EXPLAIN shows the join actually
occurring when selecting b from the view quux. If I understand
correctly (maybe I don't), this is guaranteed to be exactly the same as
the selecting b directly from the bar table. The practical import of
this comes into play when views are provided to simplify queries for end
users, and those views use joins to include related data. If the user
enters a query that is equivalent to a query on a base table, why should
the query pay a performance penalty ?
table foo:
Column | Type | Modifiers
+-+---
a | integer | not null
Indexes:
"foo_pkey" PRIMARY KEY, btree (a)
table bar:
Column | Type | Modifiers
+-+---
a | integer | not null
b | integer |
Foreign-key constraints:
"bar_a_fkey" FOREIGN KEY (a) REFERENCES foo(a)
view quux:
Column | Type | Modifiers
+-+---
a | integer |
b | integer |
View definition:
SELECT bar.a, bar.b
FROM bar
NATURAL JOIN foo
EXPLAINed Queries:
explain select b from bar;
QUERY PLAN
---
Seq Scan on bar (cost=0.00..1.00 rows=1 width=4)
(1 row)
explain select b from quux;
QUERY PLAN
--
Nested Loop (cost=0.00..5.84 rows=1 width=4)
-> Seq Scan on bar (cost=0.00..1.00 rows=1 width=8)
-> Index Scan using foo_pkey on foo (cost=0.00..4.82 rows=1
width=4)
Index Cond: ("outer".a = foo.a)
(4 rows)
--
Jacob Costello <[EMAIL PROTECTED]>
Sun Trading, LLC
---(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
[PERFORM] Size and performance hit from using UTF8 vs. ASCII?
I'm specifically interested in the default C Locale; but if there's a difference in the answer for other locales, I'd like to hear about that as well. Thanks in Advance, Ron ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] optimizing away join when querying view
Jacob Costello <[EMAIL PROTECTED]> writes: > Postgres doesn't seem to optimize away unnecessary joins There is no such thing as an unnecessary join, unless you are willing to stake the correctness of the query on constraints that could be dropped after the query is planned. Until we have some infrastructure to deal with that situation, nothing like this is going to happen. regards, tom lane ---(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] optimizing away join when querying view
On Wed, 8 Feb 2006, Jacob Costello wrote: > Postgres doesn't seem to optimize away unnecessary joins in a view > definition when the view is queried in such a way that the join need not > be executed. In the example below, I define two tables, foo and bar, > with a foreign key on bar referencing foo, and a view on the natural > join of the tables. The tables are defined so that the relationship > from bar to foo is allowed to be many to one, with the column of bar > referencing foo (column a) set NOT NULL, so that there must be exactly > one foo record for every bar record. I then EXPLAIN selecting the "b" > column from bar, through the view and from bar directly. The tables > have been ANALYZEd but have no data. EXPLAIN shows the join actually > occurring when selecting b from the view quux. If I understand > correctly (maybe I don't), this is guaranteed to be exactly the same as > the selecting b directly from the bar table. AFAIK there are periods in which a foreign key does not guarantee that there's one foo record for every bar record between an action and the constraint check for that action at statement end so you'd probably have to be careful in any case. ---(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] Size and performance hit from using UTF8 vs. ASCII?
On Wed, 2006-02-08 at 09:11 -0500, Ron wrote: > I'm specifically interested in the default C Locale; but if there's a > difference in the answer for other locales, I'd like to hear about > that as well. The size hit will be effectively zero if your data is mainly of the ASCII variety, since ASCII printable characters to UTF-8 is an identity transform. However anything involving string comparisons, including equality, similarity (LIKE, regular expressions), or any other kind of comparison (ORDER BY, GROUP BY) will be slower. In my experience the performance hit varies from zero to 100% in CPU time. UTF-8 is never faster that ASCII, as far as I know. However, if you need UTF-8 then you need it, and there's no point in worrying about the performance hit. You may as well just do two benchmark runs with your database initialized in either character set to see for yourself. -jwb ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Default autovacuum settings too conservative
Hi, Mahesh, Mahesh Shinde wrote: > Does vacuum improves the performance of the database search.. As if now I > have a table who is having a records 70 lac and daily appx 10-15 thousand > rows get added. so please let me know which type of vacuum I should prefer. > I am accessing a data using java application which is hosted on the same > database server. I don't know what "70 lac" means. But if you only add to the table, and never update or delete, vacuum brings nothing for performance. (Although it is necessary for TID wraparound prevention.) However, if your often do range queries on an index that does not correspond to the insertion order, you may benefit from CLUSTERing on that index from time to time. 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 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] Sane configuration options for a WinXP laptop 8.1 install?
In an attempt to save myself some time, I thought I ask The Community if anyone has guidance here. HW: Intel PM (very likely to be upgraded to an AMD Turion when the proper HW becomes available) w/ 2GB of RAM (shortly to be 4GB) and a 5400rpm 100GB HD (will be dual 7200rpm 160GB HD's as soon as they become available) OS: The laptop in question is running the latest WinXP service pack and patches. When the CPU and HD upgrades mentioned above happen, I will probably start running dual boot FC5 + 64b Windows. Possible optional HW: external "box of HDs" for doing and/or modelling stuff that can't be using only the internal ones. I want to get as much performance as I can out of the HW + OS. Anyone want to take a stab at what the config files and options should be for best performance under most circumstances? This is intended to be a portable development platform, so opinions as to which contrib modules are worth/not worth installing is also appreciated. TiA, Ron ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Default autovacuum settings too conservative
Markus Schaber wrote: Does vacuum improves the performance of the database search.. As if now I have a table who is having a records 70 lac and daily appx 10-15 thousand rows get added. so please let me know which type of vacuum I should prefer. I am accessing a data using java application which is hosted on the same database server. I don't know what "70 lac" means. One lac (also spelt "lakh") is one hundred thousand. And one crore is ten million. Indians count differently from the rest of the world :-). Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ ---(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] Default autovacuum settings too conservative
Hi, Tim, Tim Allen schrieb: >> I don't know what "70 lac" means. > One lac (also spelt "lakh") is one hundred thousand. And one crore is > ten million. Indians count differently from the rest of the world :-). Okay, so he talks about 7 million rows. Thank you. Markus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Sane configuration options for a WinXP laptop 8.1 install?
On Wed, Feb 08, 2006 at 05:05:02PM -0500, Ron wrote: > In an attempt to save myself some time, I thought I ask The Community > if anyone has guidance here. > > HW: Intel PM (very likely to be upgraded to an AMD Turion when the > proper HW becomes available) w/ 2GB of RAM (shortly to be 4GB) and a > 5400rpm 100GB HD (will be dual 7200rpm 160GB HD's as soon as they > become available) > > OS: The laptop in question is running the latest WinXP service pack > and patches. When the CPU and HD upgrades mentioned above happen, I > will probably start running dual boot FC5 + 64b Windows. > > Possible optional HW: external "box of HDs" for doing and/or > modelling stuff that can't be using only the internal ones. > > I want to get as much performance as I can out of the HW + OS. > > Anyone want to take a stab at what the config files and options > should be for best performance under most circumstances? > > This is intended to be a portable development platform, so opinions > as to which contrib modules are worth/not worth installing is also > appreciated. Off the top of my head... shared_buffers=3 drop max_connections to what you'll actually be using maintenance_work_mem=10 work_mem=200/max_connections (maybe * 0.9 for some added margin) autovacuum=on autovacuum_vacuum_cost_delay=20 autovacuum_vacuum_scale_factor=0.2 -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
