[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] Performance With Joins on Large Tables
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) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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
Re: [PERFORM] Performance With Joins on Large Tables
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 ---(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 Wed, 2006-09-13 at 10:19 -0600, Joshua Marsh wrote: > > 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). 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Performance With Joins on Large Tables
> Is there anything I'm missing that is preventing it from using the index? It > just seems weird to me that other joins like this work fine and fast > with indexes, > but this one won't. Did You consider clustering both tables on the dsiacctno index? I just checked that for a 4M rows table even with enable_seqscan=on and default *page_cost on PG 8.1.4 an index scan is being chosen for select * from table order by serial_pkey_field This is essentially the question in Your case - sort it, or get it sorted via the index at the expense of more random IO. I think clustering should work for You, but I am no expert, check with others. Greetings Marcin ---(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
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] Performance With Joins on Large Tables
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 ---(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, 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
"Joshua Marsh" <[EMAIL PROTECTED]> writes: > I have a suspision that pgsql isn't tuned to properly deal with tables > of this size. Actually, it is. Most of the planner complaints we get are from people whose tables fit in memory and they find that the default planner behavior doesn't apply real well to that case. I find your indexscan-is-faster-than-sort results pretty suspicious for 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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
"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? > 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
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
"Joshua Marsh" <[EMAIL PROTECTED]> writes: >>> On 9/13/06, Tom Lane <[EMAIL PROTECTED]> wrote: 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) Wow, that correlation value is *way* away from order. If they were really in exact order by dsiacctno then I'd expect to see 1.0 in that column. Can you take another look at the tables and confirm the ordering? Does the correlation change if you do an ANALYZE on the tables? (Some small change is to be expected due to random sampling, but this is way off.) regards, tom lane ---(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
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.