Re: [PERFORM] Configuration settings for 32GB RAM server
On 12/4/06, Mark Lonsdale <[EMAIL PROTECTED]> wrote: Maintenance_work_mem = 1048576 – Figured Id allocate 1GB for this. Do you know how often and when you will be creating indexes or clustering? We set ours to 2GB because of the performance gains. We've also thought about testing it at 4GB. We can do this because we know during the middle of the night our server load drops to nearly zero. If you know you have windows like that, then I would definitely suggest increasing your maintenance_work_mem. It's halved the time for io intesive tasks like cluster.
Re: [PERFORM] start up cost estimate
On 11/18/06, rakesh kumar <[EMAIL PROTECTED]> wrote: Hi , I wanted to know , how the start up cost is computed in postgresql . can u give me an example to illustrate the estimation of start up cost . thanku raa . It would be very helpful to have a lot more information. Some questions that come to mind: Approximately how many records will you be storing? How big do you think your biggest tables will be? How frequently will your biggest tables be accessed (x/day, x/second, x/week)? Will those accesses be read only, or read/write? How important is availability vs. cost? Who is your favorite Irish folk singer?
Re: [PERFORM] Postgresql 8.1.4 - performance issues for select on view using max
On 10/18/06, Ioana Danes <[EMAIL PROTECTED]> wrote: # explain select max(transid) from public.transaction; QUERYPLAN -- Result (cost=0.04..0.05 rows=1 width=0) InitPlan -> Limit (cost=0.00..0.04 rows=1 width=8) -> Index Scan Backward usingpk_transaction on transaction (cost= 0.00..357870.46rows=9698002 width=8) Filter: (transid IS NOT NULL)(5 rows)This works fine because i recognizes the index for that table and can simply use it to find the max. 2. Select from the view is doing a sequential scan:--- # explain analyze select max(transid) fromalltransaction;QUERY PLAN--- - Aggregate (cost=200579993.70..200579993.71 rows=1width=8) (actual time=115778.101..115778.103 rows=1loops=1) -> Append (cost=1.00..200447315.74rows=10614237 width=143) (actual time=0.082..95146.144 rows=10622206 loops= 1) -> Seq Scan transaction(cost=1.00..100312397.02 rows=9698002width=143) (actual time=0.078..56002.778 rows= 9706475 loops=1) -> Seq Scan on transaction (cost=1.00..100028776.35 rows=916235width=143) (actual time=8.822..2799.496 rows= 915731 loops=1) Total runtime: 115778.200 ms(5 rows)Because this is a view, it cannot use the indexes from the other tables. Everytime you run a query against a view, it recreates itself based on the underlying data. From there it must sort the table based on the i and then return your max. It's probably not a great idea to make a view this way if you are planning on using queries like this regularly because you can't create an index for a view. You could try a query that pulls the max from each table and then grabs the max of these: select max (foo.transid) from (select max(transid) as id from public.transaction union select max(transid) from archive.transaction) as foo;-- This E-mail is covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521 and is legally privileged.This information is confidential information and is intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.
Re: [PERFORM] Hints proposal
On 10/12/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote: Posting here instead of hackers since this is where the thread gotstarted...The argument has been made that producing a hints system will be as hard as actually fixing the optimizer. There's also been clamoring for anactual proposal, so here's one that (I hope) wouldn't be very difficultto implemen.My goal with this is to keep the coding aspect as simple as possible, so that implementation and maintenance of this isn't a big burden. Towardsthat end, these hints either tell the planner specifically how to handlesome aspect of a query, or they tell it to modify specific cost estimates. My hope is that this information could be added to theinternal representation of a query without much pain, and that theplanner can then use that information when generating plans. I've been following the last thread with a bit of interest. I like the proposal. It seems simple and easy to use. What is it about hinting that makes it so easily breakable with new versions? I don't have any experience with Oracle, so I'm not sure how they screwed logic like this up. Hinting to use a specific merge or scan seems fairly straight forward; if the query requests to use an index on a join, I don't see how hard it is to go with the suggestion. It will become painfully obvious to the developer if his hinting is broken.
Re: [PERFORM] Performance With Joins on Large Tables
Wow, that correlation value is *way* away from order. If they werereally in exact order by dsiacctno then I'd expect to see
1.0 inthat column. Can you take another look at the tables and confirmthe ordering? Does the correlation change if you do an ANALYZE on thetables? (Some small change is to be expected due to random sampling,
but this is way off.) regards, tom lane
Thanks for pointing that out. Generally we load the tables via COPY and then never touch the data. Because of the slowdown, I have been updating tuples. I reloaded it from scratch, set enable_seqscan=off and random_access_age=4 and I got the results I was looking for:
data=# analyze view_505;ANALYZEdata=# analyze r3s169;ANALYZEdata=# select tablename, attname, n_distinct, avg_width, correlation from pg_stats where tablename in ('view_505', 'r3s169') and attname = 'dsiacctno';
tablename | attname | n_distinct | avg_width | correlation---+---++---+- view_505 | dsiacctno | -1 | 13 | 1 r3s169 | dsiacctno | 42140 | 13 | 1
(2 rows)
data=# explain SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM v.custdate), EXTRACT (EPOCH FROM s.custdate) FROM view_505 v INNER JOIN r3s169 s on v.dsiacctno = s.dsiacctno; QUERY PLAN
-- Merge Join (cost=0.00..20099712.79 rows=285153952 width=11) Merge Cond: ("outer".dsiacctno = "inner".dsiacctno)
-> Index Scan using view_505_dsiacctno on view_505 v (cost=0.00..5147252.74 rows=112282976 width=20) -> Index Scan using r3s169_dsiacctno on r3s169 s (cost=0.00..8256331.47 rows=285153952 width=17)
(4 rows)
Thanks for you help everyone.
Re: [PERFORM] Performance With Joins on Large Tables
On 9/13/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Joshua Marsh" <[EMAIL PROTECTED]> writes:
>> Are the tables perhaps nearly in order by the dsiacctno fields?
> My assumption would be they are in exact order. The text file I used
> in the COPY statement had them in order, so if COPY preserves that in
> the database, then it is in order.
Ah. So the question is why the planner isn't noticing that. What do
you see in the pg_stats view for the two dsiacctno fields --- the
correlation field in particular?
Here are the results:
data=# select tablename, attname, n_distinct, avg_width, correlation
from pg_stats where tablename in ('view_505', 'r3s169') and attname =
'dsiacctno';
tablename | attname | n_distinct | avg_width | correlation
---+---++---+-
view_505 | dsiacctno | -1 |13 |-0.13912
r3s169| dsiacctno | 44156 |13 | -0.126824
(2 rows)
Someone suggested CLUSTER to make sure they are in fact ordered, I can
try that to and let everyone know the results.
> The system has 8GB of ram and work_mem is set to 256MB.
Seems reasonable enough. BTW, I don't think you've mentioned exactly
which PG version you're using?
regards, tom lane
I am using 8.0.3.
---(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
Re: [PERFORM] Performance With Joins on Large Tables
Are the tables perhaps nearly in order by the dsiacctno fields? If that were the case, and the planner were missing it for some reason, these results would be plausible. BTW, what are you using for work_mem, and how does that compare to your available RAM? regards, tom lane My assumption would be they are in exact order. The text file I used in the COPY statement had them in order, so if COPY preserves that in the database, then it is in order. The system has 8GB of ram and work_mem is set to 256MB. I'll see if I can't make time to run the sort-seqscan method so we can have an exact time to work with. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Performance With Joins on Large Tables
On 9/13/06, Terje Elde <[EMAIL PROTECTED]> wrote:
Jeff Davis wrote:
> Is it overestimating the cost of using indexes or underestimating the
> cost of a seq scan, or both? Maybe explain with the 0.1 setting will
> help?
>
If enable_seqscan is off, and cost is still set to 1, it could
be that it's quite simply forcibly underestimating the cost of a seqscan
in this case.
If enable_secscan was off for the mentioned plan, it'd be interesting to
see if things would be saner with seqscans enabled, and a more
reasonable random page cost. If more 'sane' values still produce the
desired plan, it might be better for other plans etc.
Terje
I turned enable_seqscan to off and got similar results.
random_age_cost at 4.0:
data=# explain SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
v.custdate), EXTRACT (EPOCH FROM s.custdate) FROM view_505 v INNER
JOIN r3s169 s on v.dsiacctno = s.dsiacctno;
QUERY PLAN
---
Merge Join (cost=293737539.01..301430139.34 rows=285230272 width=11)
Merge Cond: ("outer".dsiacctno = "inner".dsiacctno)
-> Sort (cost=127311593.00..127592577.62 rows=112393848 width=20)
Sort Key: v.dsiacctno
-> Seq Scan on view_505 v (cost=1.00..104602114.48
rows=112393848 width=20)
-> Sort (cost=166425946.01..167139021.69 rows=285230272 width=17)
Sort Key: s.dsiacctno
-> Seq Scan on r3s169 s (cost=1.00..106873675.72
rows=285230272 width=17)
(8 rows)
random_page_cost at 3.0:
data=# explain SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
v.custdate), EXTRACT (EPOCH FROM s.custdate) FROM view_505 v INNER
JOIN r3s169 s on v.dsiacctno = s.dsiacctno;
QUERY PLAN
---
Merge Join (cost=288303269.01..295995869.34 rows=285230272 width=11)
Merge Cond: ("outer".dsiacctno = "inner".dsiacctno)
-> Sort (cost=125775957.00..126056941.62 rows=112393848 width=20)
Sort Key: v.dsiacctno
-> Seq Scan on view_505 v (cost=1.00..104602114.48
rows=112393848 width=20)
-> Sort (cost=162527312.01..163240387.69 rows=285230272 width=17)
Sort Key: s.dsiacctno
-> Seq Scan on r3s169 s (cost=1.00..106873675.72
rows=285230272 width=17)
(8 rows)
random_age_cost ad 2,0:
data=# explain SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
v.custdate), EXTRACT (EPOCH FROM s.custdate) FROM view_505 v INNER
JOIN r3s169 s on v.dsiacctno = s.dsiacctno;
QUERY PLAN
---
Merge Join (cost=282868999.01..290561599.34 rows=285230272 width=11)
Merge Cond: ("outer".dsiacctno = "inner".dsiacctno)
-> Sort (cost=124240321.00..124521305.62 rows=112393848 width=20)
Sort Key: v.dsiacctno
-> Seq Scan on view_505 v (cost=1.00..104602114.48
rows=112393848 width=20)
-> Sort (cost=158628678.01..159341753.69 rows=285230272 width=17)
Sort Key: s.dsiacctno
-> Seq Scan on r3s169 s (cost=1.00..106873675.72
rows=285230272 width=17)
(8 rows)
random_page_cost at 1.0:
data=# explain SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
v.custdate), EXTRACT (EPOCH FROM s.custdate) FROM view_505 v INNER
JOIN r3s169 s on v.dsiacctno = s.dsiacctno;
QUERY PLAN
Merge Join (cost=154730044.01..274040257.41 rows=285230272 width=11)
Merge Cond: ("outer".dsiacctno = "inner".dsiacctno)
-> Index Scan using view_505_dsiacctno on view_505 v
(cost=0.00..111923570.63 rows=112393848 width=20)
-> Sort (cost=154730044.01..155443119.69 rows=285230272 width=17)
Sort Key: s.dsiacctno
-> Seq Scan on r3s169 s (cost=1.00..106873675.72
rows=285230272 width=17)
(6 rows)
random_page_cost ad 0.1:
data=# explain SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
v.custdate), EXTRACT (EPOCH FROM s.custdate) FROM view_505 v INNER
JOIN r3s169 s on v.dsiacctno = s.dsiacctno;
QUERY PLAN
---
Merge Join (cost=0.00..51808909.26 rows=285230272 width=11)
Merge Cond: ("outer".dsiacctno = "inner".dsiacctno)
-> Index Scan using view_505_dsiacctno on view_505 v
(cost=0.00..12755411.69 rows=112393848 width=20)
-> Index Scan using r3s169_dsiacctno on r3s169 s
(cost=0.00..32357747.90 rows=285230272 width=17)
(4 rows)
I have a suspision that pgsql isn't tuned to properly deal with tables
of this size. Are there other things I should look at when dealing
with a database o
Re: [PERFORM] Performance With Joins on Large Tables
Hmm... that sounds bad. I'm sure your system will always choose indexes
with that value.
Is it overestimating the cost of using indexes or underestimating the
cost of a seq scan, or both? Maybe explain with the 0.1 setting will
help?
Regards,
Jeff Davis
data=# explain SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
v.custdate), EXTRACT (EPOCH FROM s.custdate) FROM view_505 v INNER
JOIN r3s169 s on v.dsiacctno = s.dsiacctno;
QUERY PLAN
---
Merge Join (cost=0.00..51808909.26 rows=285230272 width=11)
Merge Cond: ("outer".dsiacctno = "inner".dsiacctno)
-> Index Scan using view_505_dsiacctno on view_505 v
(cost=0.00..12755411.69 rows=112393848 width=20)
-> Index Scan using r3s169_dsiacctno on r3s169 s
(cost=0.00..32357747.90 rows=285230272 width=17)
(4 rows)
This is what I wanted, two index scans. Just to give you an idea of
the difference in time, this plan would allow me to process 100,000
records ever few seconds, while the sequential scan would only
produces 100,000 every 10 minutes.
---(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] Query Progress (was: Performance With Joins on Large Tables)
On 9/13/06, Bucky Jordan <[EMAIL PROTECTED]> wrote: Setting to 0.1 finally gave me the result I was looking for. I know that the index scan is faster though. The seq scan never finished (i killed it after 24+ hours) and I'm running the query now with indexes and it's progressing nicely (will probably take 4 hours). In regards to "progressing nicely (will probably take 4 hours)" - is this just an estimate or is there some way to get progress status (or something similar- e.g. on step 6 of 20 planned steps) on a query in pg? I looked through Chap 24, Monitoring DB Activity, but most of that looks like aggregate stats. Trying to relate these to a particular query doesn't really seem feasible. This would be useful in the case where you have a couple of long running transactions or stored procedures doing analysis and you'd like to give the user some feedback where you're at. Thanks, Bucky I do it programmatically, not through postgresql. I'm using a cursor, so I can keep track of how many records I've handled. I'm not aware of a way to do this in Postgresql. ---(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
Re: [PERFORM] Performance With Joins on Large Tables
On 9/13/06, Jeff Davis <[EMAIL PROTECTED]> wrote:
On Wed, 2006-09-13 at 08:49 -0600, Joshua Marsh wrote:
> That seems to have done it. Are there any side effects to this
> change? I read about random_page_cost in the documentation and it
> seems like this is strictly for planning. All the tables on this
> database will be indexed and of a size similar to these two, so I
> don't see it causing any other problems. Though I would check though
> :)
>
Right, it's just used for planning. Avoid setting it too low, if it's
below about 2.0 you would most likely see some very strange plans.
Certainly it doesn't make sense at all to set it below 1.0, since that
is saying it's cheaper to get a random page than a sequential one.
What was your original random_page_cost, and what is the new value you
set it to?
Regards,
Jeff Davis
I tried it at several levels. It was initially at 4 (the default). I
tried 3 and 2 with no changes. When I set it to 1, it used and index
on view_505 but no r3s169:
data=# EXPLAIN SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
v.custdate), EXTRACT (YEAR FROM s.custdate) || '-' || EXTRACT (MONTH
FROM s.custdate) FROM view_505 v INNER JOIN r3s169 s ON v.dsiacctno =
s.dsiacctno;
QUERY PLAN
Merge Join (cost=154730044.01..278318711.49 rows=285230272 width=11)
Merge Cond: ("outer".dsiacctno = "inner".dsiacctno)
-> Index Scan using view_505_dsiacctno on view_505 v
(cost=0.00..111923570.63 rows=112393848 width=20)
-> Sort (cost=154730044.01..155443119.69 rows=285230272 width=17)
Sort Key: s.dsiacctno
-> Seq Scan on r3s169 s (cost=1.00..106873675.72
rows=285230272 width=17)
Setting to 0.1 finally gave me the result I was looking for. I know
that the index scan is faster though. The seq scan never finished (i
killed it after 24+ hours) and I'm running the query now with indexes
and it's progressing nicely (will probably take 4 hours).
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance With Joins on Large Tables
On 9/13/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
On Tue, Sep 12, 2006 at 04:17:34PM -0600, Joshua Marsh wrote:
> data=# EXPLAIN SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
> v.custdate), EXTRACT (YEAR FROM s.custdate) || '-' || EXTRACT (MONTH
> FROM s.custdate) FROM view_505 v INNER JOIN r3s169 s ON v.dsiacctno =
> s.dsiacctno;
> QUERY PLAN
>
---
> Merge Join (cost=293767607.69..305744319.52 rows=285392608 width=11)
> Merge Cond: ("outer".dsiacctno = "inner".dsiacctno)
> -> Sort (cost=127304933.87..127585815.71 rows=112352736 width=20)
> Sort Key: v.dsiacctno
> -> Seq Scan on view_505 v (cost=1.00..104604059.36
> rows=112352736 width=20)
> -> Sort (cost=166462673.82..167176155.34 rows=285392608 width=17)
> Sort Key: s.dsiacctno
> -> Seq Scan on r3s169 s (cost=1.00..106875334.08
> rows=285392608 width=17)
> (8 rows)
>
>
> Since enable_seqscan is off, my understanding is that in order for the query
> planner to user a sequential scan it must think there is no other
> alternative.
> Both sides are indexed and anaylzed, so that confuses me a little.
>
> I tried it on a smaller sample set of the data and it works fine:
Actually, enable_seqscan=off just adds a fixed overhead to the seqscan
cost estimate. That's why the cost for the seqscans in that plan starts
at 1. I've suggested changing that to a variable overhead based
on the expected rowcount, but the counter-argument was that anyone with
so much data that the fixed amount wouldn't work would most likely be
having bigger issues anyway.
Other things you can try to get the index scan back would be to reduce
random_page_cost and to analyze the join fields in those tables with a
higher statistics target (though I'm not 100% certain the join cost
estimator actually takes that into account). Or if you don't mind
patching your source code, it wouldn't be difficult to make
enable_seqscan use a bigger 'penalty value' than 1000.
--
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Thanks for the tip. I lowered random_page_cost and got these results:
data=# EXPLAIN SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
v.custdate), EXTRACT (YEAR FROM s.custdate) || '-' || EXTRACT (MONTH
FROM s.custdate) FROM view_505 v INNER JOIN r3s169 s ON v.dsiacctno =
s.dsiacctno;
QUERY PLAN
--
Merge Join (cost=0.00..20921221.49 rows=285230272 width=11)
Merge Cond: ("outer".dsiacctno = "inner".dsiacctno)
-> Index Scan using view_505_dsiacctno on view_505 v
(cost=0.00..2838595.79 rows=112393848 width=20)
-> Index Scan using r3s169_dsiacctno on r3s169 s
(cost=0.00..7106203.68 rows=285230272 width=17)
(4 rows)
That seems to have done it. Are there any side effects to this
change? I read about random_page_cost in the documentation and it
seems like this is strictly for planning. All the tables on this
database will be indexed and of a size similar to these two, so I
don't see it causing any other problems. Though I would check though
:)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[PERFORM] Performance With Joins on Large Tables
I am having problems performing a join on two large tables. It seems to only
want to use a sequential scan on the join, but that method seems to be slower
than an index scan. I've never actually had it complete the sequential scan
because I stop it after 24+ hours. I've run joins against large tables before
and an index scan was always faster (a few hours at the most).
Here is some information on the two tables:
data=# analyze view_505;
ANALYZE
data=# analyze r3s169;
ANALYZE
data=# \d view_505
Table "public.view_505"
Column | Type | Modifiers
--+---+---
dsiacctno| numeric |
name | boolean |
title| boolean |
company | boolean |
zip4 | boolean |
acceptcall | boolean |
phonedirect | smallint |
phonetollfree| smallint |
fax | smallint |
editdrop | boolean |
postsuppress | boolean |
firstnameinit| boolean |
prefix | integer |
crrt | boolean |
dpbc | boolean |
executive| integer |
addressline | integer |
multibuyer | integer |
activemultibuyer | integer |
active | boolean |
emails | integer |
domains | integer |
zip1 | character varying(1) |
zip3 | character varying(3) |
gender | character varying(1) |
topdomains | bit varying |
city | character varying(35) |
state| character varying(35) |
zip | character varying(20) |
country | character varying(30) |
selects | bit varying |
files| integer[] |
sics | integer[] |
custdate | date |
Indexes:
"view_505_city" btree (city)
"view_505_dsiacctno" btree (dsiacctno)
"view_505_state" btree (state)
"view_505_zip" btree (zip)
"view_505_zip1" btree (zip1)
"view_505_zip3" btree (zip3)
data=# \d r3s169
Table "public.r3s169"
Column| Type | Modifiers
-++---
dsiacctno | numeric|
fileid | integer|
customerid | character varying(20) |
email | character varying(100) |
sic2| character varying(2) |
sic4| character varying(4) |
sic6| character varying(6) |
custdate| date |
inqdate | date |
eentrydate | date |
esubdate| date |
efaildate | date |
eunlistdate | date |
pentrydate | date |
psubdate| date |
punlistdate | date |
pexpiredate | date |
lastupdate | date |
emaildrop | numeric|
sic8| character varying(8) |
Indexes:
"r3s169_dsiacctno" btree (dsiacctno)
data=# select count(*) from view_505;
count
---
112393845
(1 row)
data=# select count(*) from r3s169;
count
---
285230264
(1 row)
Here is what EXPLAIN says:
data=# EXPLAIN SELECT v.phonedirect, v.editdrop, EXTRACT (EPOCH FROM
v.custdate), EXTRACT (YEAR FROM s.custdate) || '-' || EXTRACT (MONTH
FROM s.custdate) FROM view_505 v INNER JOIN r3s169 s ON v.dsiacctno =
s.dsiacctno;
QUERY PLAN
---
Merge Join (cost=293767607.69..305744319.52 rows=285392608 width=11)
Merge Cond: ("outer".dsiacctno = "inner".dsiacctno)
-> Sort (cost=127304933.87..127585815.71 rows=112352736 width=20)
Sort Key: v.dsiacctno
-> Seq Scan on view_505 v (cost=1.00..104604059.36
rows=112352736 width=20)
-> Sort (cost=166462673.82..167176155.34 rows=285392608 width=17)
Sort Key: s.dsiacctno
-> Seq Scan on r3s169 s (cost=1.00..106875334.08
rows=285392608 width=17)
(8 rows)
I can't really do and EXPLAIN ANALYZE because the query never really finishes.
Also, I use a cursor to loop through the data. view_505 isn't a pgsql view, its
just how we decided to name the table. There is a one to many
relationship between
view_505 and r3s169.
Since enable_seqscan is off, my understanding is that in order for the query
planner to user a sequential scan it must think there is no other alternative.
Both sides are indexed and anaylzed, so that confuses me a little.
I tried it on a smaller sample set of the data and it works fine:
data=# select * into r3s169_test from r3s169 limit 100;
SELE
Re: [PERFORM] Hardware/OS recommendations for large databases (
On 11/17/05, William Yu <[EMAIL PROTECTED]> wrote: > No argument there. But it's pointless if you are IO bound.Why would you just accept "we're IO bound, nothing we can do"? I'd doeverything in my power to make my app go from IO bound to CPU bound -- whether by optimizing my code or buying more hardware. I can tell you ifour OLTP servers were IO bound, it would run like crap. Instead of < 1sec, we'd be looking at 5-10 seconds per "user transaction" and our users would be screaming bloody murder.In theory, you can always convert your IO bound DB to CPU bound bystuffing more and more RAM into your server. (Or partitioning the DBacross multiple servers.) Whether it's cost effective depends on the DB and how much your users are paying you -- and that's a case-by-caseanalysis. Not a global statement of "IO-bound, pointless". We all want our systems to be CPU bound, but it's not always possible. Remember, he is managing a 5 TB Databse. That's quite a bit different than a 100 GB or even 500 GB database.
Re: [PERFORM] Help speeding up delete
On 11/14/05, Steve Wampler <[EMAIL PROTECTED]> wrote: However, even that seems to have a much higher cost than I'd expect: lab.devel.configdb=# explain delete from "tmp_table2" where id in(select id from tmp_table2 where name='obsid' and value = 'oid080505'); NOTICE: QUERY PLAN: Seq Scan on tmp_table2 (cost=0.00..65705177237.26 rows=769844 width=6) SubPlan -> Materialize (cost=42674.32..42674.32 rows=38 width=50) -> Seq Scan on tmp_table2 (cost=0.00..42674.32 rows=38 width=50) For one reason or the other, the planner things a sequential scan is the best solution. Try turning off seq_scan before the query and see if it changes the plan (set enable_seqscan off;). I've seen this problem with sub queries and that usually solves it. -- This E-mail is covered by the Electronic Communications Privacy Act, 18 U.S.C. 2510-2521 and is legally privileged.This information is confidential information and is intended only for the use of the individual or entity named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.
Re: [PERFORM] Large Database Performance suggestions
Thanks for all of your help so far. Here is some of the information you guys were asking for: Test System: 2x AMD Opteron 244 (1.8Ghz) 8GB RAM 7x 72GB SCSI HDD (Raid 5) postrgesql.conf information: #--- # RESOURCE USAGE (except WAL) #--- # - Memory - shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each #sort_mem = 1024# min 64, size in KB #vacuum_mem = 8192 # min 1024, size in KB sort_mem = 4096000 vacuum_mem = 1024000 # - Free Space Map - #max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~50 bytes each # - Kernel Resource Usage - #max_files_per_process = 1000 # min 25 #preload_libraries = '' #--- # WRITE AHEAD LOG #--- # - Settings - #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: # fsync, fdatasync, open_sync, or open_datasync #wal_buffers = 8# min 4, 8KB each # - Checkpoints - #checkpoint_segments = 3# in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30# 0 is off, in seconds #commit_delay = 0 # range 0-10, in microseconds #commit_siblings = 5# range 1-1000 Everything else are at their defaults. I actually think the WAL options are set to defaults as well, but I don't recall exactly :) As for the queries and table, The data we store is confidential, but it is essentially an account number with a bunch of boolean fields that specify if a person applies to criteria. So a query may look something like: SELECT acctno FROM view_of_data WHERE has_name AND is_active_member AND state = 'OH'; which is explained as something like this: QUERY PLAN - Seq Scan on view_of_data (cost=0.00..25304.26 rows=22054 width=11) Filter: (has_name AND is_active_member AND ((state)::text = 'OH'::text)) (2 rows) Occasionally, because we store data from several sources, we will have requests for data from several sources. We simply intersect the view_of_data table with a sources table that lists what acctno belong to what source. This query would look something like this: SELECT acctno FROM view_of_data WHERE has_name AND is_active_member AND state = 'OH' INTERSECT SELECT acctno FROM sources_data WHERE source = 175; which is explained as follows: QUERY PLAN --- SetOp Intersect (cost=882226.14..885698.20 rows=69441 width=11) -> Sort (cost=882226.14..883962.17 rows=694411 width=11) Sort Key: acctno -> Append (cost=0.00..814849.42 rows=694411 width=11) -> Subquery Scan "*SELECT* 1" (cost=0.00..25524.80 rows=22054 width=11) -> Seq Scan on view_of_data (cost=0.00..25304.26 rows=22054 width=11) Filter: (has_name AND is_active_member AND ((state)::text = 'OH'::text)) -> Subquery Scan "*SELECT* 2" (cost=0.00..789324.62 rows=672357 width=11) -> Seq Scan on sources_data (cost=0.00..782601.05 rows=672357 width=11) Filter: (source = 23) Again, we see our biggest bottlenecks when we get over about 50 million records. The time to execute grows exponentially from that point. Thanks again for all of your help! -Josh On Fri, 22 Oct 2004 07:38:49 -0400, Dave Cramer <[EMAIL PROTECTED]> wrote: > Josh, > > Your hardware setup would be useful too. It's surprising how slow some > big name servers really are. > If you are seriously considering memory sizes over 4G you may want to > look at an opteron. > > Dave > > > > Joshua Marsh wrote: > > >Hello everyone, > > > >I am currently working on a data project that uses PostgreSQL > >extensively to store, manage and maintain the data. We haven't had > >any problems regarding database size until recently. The three major > >tables we use never get bigger than 10 million records. With this > >size, we can do things like storing the indexes or even the tables in > >memory to allow faster access. > > > >Recently, we have found customers who are wanting to use our service > >with data files between 100 million and 300 million r
Re: [PERFORM] Slow query
Any time you run subqueries, it's going to slow down the update
process a lot. Each record that is updated in source_song_title runs
two additional queries. When I do large updates like this, I usualy
Run a transaction that will select all the new data into a new table
on a join. For example
SELECT
a.*,
b.licensing_match_order,
b.affiliation_match_order,
d.title
INTO
updated_data
FROM
source_song_title AS a
INNER JOIN
source_system AS b
ON
b.id = d.id
INNER JOIN
source_song AS c
ON
a.id = c.id
INNER JOIN
source_title AS d
ON
a.id = d.id
I'm not sure that query does what you want, but you get the idea.
Then just drop the old table and rename the updated_data table. This
way instead of doing a bunch of updates, you do one select and a
rename.
-Josh
On Fri, 22 Oct 2004 16:37:14 -0400, Tom Lane <[EMAIL PROTECTED]> wrote:
> Roger Ging <[EMAIL PROTECTED]> writes:
> > update source_song_title set
> > source_song_title_id = nextval('source_song_title_seq')
> > ,licensing_match_order = (select licensing_match_order from
> > source_system where source_system_id = ss.source_system_id)
> > ,affiliation_match_order = (select affiliation_match_order from
> > source_system where source_system_id = ss.source_system_id)
> > ,title = st.title
> > from source_song_title sst
> > join source_song ss on ss.source_song_id = sst.source_song_id
> > join source_title st on st.title_id = sst.title_id
> > where source_song_title.source_song_id = sst.source_song_id;
>
> Why is "source_song_title sst" in there? To the extent that
> source_song_id is not unique, you are multiply updating rows
> because of the self-join.
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] Large Database Performance suggestions
Hello everyone, I am currently working on a data project that uses PostgreSQL extensively to store, manage and maintain the data. We haven't had any problems regarding database size until recently. The three major tables we use never get bigger than 10 million records. With this size, we can do things like storing the indexes or even the tables in memory to allow faster access. Recently, we have found customers who are wanting to use our service with data files between 100 million and 300 million records. At that size, each of the three major tables will hold between 150 million and 700 million records. At this size, I can't expect it to run queries in 10-15 seconds (what we can do with 10 million records), but would prefer to keep them all under a minute. We did some original testing and with a server with 8GB or RAM and found we can do operations on data file up to 50 million fairly well, but performance drop dramatically after that. Does anyone have any suggestions on a good way to improve performance for these extra large tables? Things that have come to mind are Replication and Beowulf clusters, but from what I have recently studied, these don't do so wel with singular processes. We will have parallel process running, but it's more important that the speed of each process be faster than several parallel processes at once. Any help would be greatly appreciated! Thanks, Joshua Marsh P.S. Off-topic, I have a few invitations to gmail. If anyone would like one, let me know. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
