Обсуждение: Hash Join vs Nested Loops in 7.2.1 ...
I have a 7.2.1 query with two peculiar characteristics and wondered if anyone could offer some insight. First, my query takes 90 seconds with a hash join, but only 1 second with nested loops. Second, the same query sometimes takes 10-50 seconds shortly after possibly a dump or other high-data-volume queries are executed, after which it then returns to 1 second execution time. Getting crowded out of shared memory? Finally, I am inclined to turn off hash joins altogether. What should I expect to lose in doing so? Schema, query, and plans shown below... Thanks, Ed -- 700,000 rows CREATE TABLE story ( value TEXT, key SERIAL ); CREATE UNIQUE INDEX story_pkey ON story USING btree (key); -- 700,000 rows CREATE TABLE dict ( word VARCHAR, key SERIAL ); CREATE UNIQUE INDEX dict_pkey ON dict USING btree (key); CREATE UNIQUE INDEX dict_word_key ON dict USING btree (word); -- 28,000,000 rows CREATE TABLE story_dict ( dictkey INTEGER NOT NULL, storykey INTEGER NOT NULL ); CREATE UNIQUE INDEX story_dict_pkey ON story_dict(dictkey, storykey); CREATE INDEX story_dict_tk_idx ON story_dict(storykey); -- Query: -- ====== SELECT DISTINCT ftd1.storykey FROM story_dict ftd1, dict d1 WHERE d1.word = 'foo' AND d1.key = ftd1.dictkey AND EXISTS ( SELECT ft2.key FROM story ft2, story_dict ftd2, dict d2 WHERE d2.word = 'bar' AND d2.key = ftd2.dictkey AND ftd2.storykey = ft2.key AND ftd2.storykey = ftd1.storykey) AND EXISTS ( SELECT ft3.key FROM story ft3, story_dict ftd3, dict d3 WHERE d3.word = 'baz' AND d3.key = ftd3.dictkey AND ftd3.storykey = ft3.key AND ftd3.storykey = ftd1.storykey) ORDER BY ftd1.storykey LIMIT 1000; Plan with PGOPTIONS = "-fn": Limit (cost=15409053054.71..15409053054.73 rows=1 width=12) -> Unique (cost=15409053054.71..15409053054.73 rows=1 width=12) -> Sort (cost=15409053054.71..15409053054.71 rows=9 width=12) -> Nested Loop (cost=100000000.00..15409053054.57 rows=9 width=12) -> Index Scan using word_idx on dict d1 (cost=0.00..5.98 rows=1 width=4) -> Index Scan using story_dict_pkey on story_dict ftd1 (cost=0.00..15309052993.63 rows=4398 width=8) SubPlan -> Merge Join (cost=429157.62..435130.62 rows=1 width=16) -> Sort (cost=13.11..13.11 rows=1 width=12) -> Hash Join (cost=5.98..13.10 rows=1 width=12) -> Index Scan using story_dict_tk_idx on story_dict ftd2 (cost=0.00..6.59 rows=106 width=8) -> Hash (cost=5.98..5.98 rows=1 width=4) -> Index Scan using word_idx on dict d2 (cost=0.00..5.98 rows=1 width=4) -> Sort (cost=429144.50..429144.50 rows=2389196 width=4) -> Seq Scan on story ft2 (cost=0.00..86701.96 rows=2389196 width=4) -> Merge Join (cost=429157.62..435130.62 rows=1 width=16) -> Sort (cost=13.11..13.11 rows=1 width=12) -> Hash Join (cost=5.98..13.10 rows=1 width=12) -> Index Scan using story_dict_tk_idx on story_dict ftd3 (cost=0.00..6.59 rows=106 width=8) -> Hash (cost=5.98..5.98 rows=1 width=4) -> Index Scan using word_idx on dict d3 (cost=0.00..5.98 rows=1 width=4) -> Sort (cost=429144.50..429144.50 rows=2389196 width=4) -> Seq Scan on story ft3 (cost=0.00..86701.96 rows=2389196 width=4) Plan with PGOPTIONS = "-fh": Limit (cost=635283.31..635283.33 rows=1 width=12) -> Unique (cost=635283.31..635283.33 rows=1 width=12) -> Sort (cost=635283.31..635283.31 rows=9 width=12) -> Nested Loop (cost=0.00..635283.17 rows=9 width=12) -> Index Scan using word_idx on dict d1 (cost=0.00..5.98 rows=1 width=4) -> Index Scan using story_dict_pkey on story_dict ftd1 (cost=0.00..635222.22 rows=4398 width=8) SubPlan -> Nested Loop (cost=0.00..16.07 rows=1 width=16) -> Nested Loop (cost=0.00..12.00 rows=1 width=12) -> Index Scan using word_idx on dict d2 (cost=0.00..5.98 rows=1 width=4) -> Index Scan using story_dict_pkey on story_dict ftd2 (cost=0.00..6.01 rows=1 width=8) -> Index Scan using story_pkey on story ft2 (cost=0.00..4.06 rows=1 width=4) -> Nested Loop (cost=0.00..16.07 rows=1 width=16) -> Nested Loop (cost=0.00..12.00 rows=1 width=12) -> Index Scan using word_idx on dict d3 (cost=0.00..5.98 rows=1 width=4) -> Index Scan using story_dict_pkey on story_dict ftd3 (cost=0.00..6.01 rows=1 width=8) -> Index Scan using story_pkey on story ft3 (cost=0.00..4.06 rows=1 width=4)
Ed Loehr <pggeneral@bluepolka.net> writes: > I have a 7.2.1 query with two peculiar characteristics and wondered if > anyone could offer some insight. > First, my query takes 90 seconds with a hash join, but only 1 second with > nested loops. Probably because the EXISTS subplans only need to fetch one row from each table they access; that's more or less an ideal case for nestloop indexscans. Nestloops do not scale very well to large retrieval sets, however... > Second, the same query sometimes takes 10-50 seconds shortly after > possibly a dump or other high-data-volume queries are executed, after > which it then returns to 1 second execution time. Getting crowded out > of shared memory? Sounds like it. What shared-buffers setting are you using? How much RAM in the box? > Finally, I am inclined to turn off hash joins altogether. That would be a remarkably foolish thing to do. Certainly this query is not a reason to do so; AFAICS the planner will do this one just fine without any thumb on the scales. regards, tom lane
Tom Lane wrote: > >>Second, the same query sometimes takes 10-50 seconds shortly after >>possibly a dump or other high-data-volume queries are executed, after >>which it then returns to 1 second execution time. Getting crowded out >>of shared memory? > > Sounds like it. What shared-buffers setting are you using? How much > RAM in the box? shared_buffers = 256 max_fsm_relations = 500 (defaults for the rest) RAM: 2.4GB, maybe? Not that familiar with HPUX mem setup... (OS: HP-UX B.11.00 U 9000/800) $ swapinfo -mt Mb Mb Mb PCT START/ Mb TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME dev 2048 147 1901 7% 0 - 1 /dev/vg00/lvol2 reserve - 312 -312 memory 369 351 18 95% total 2417 810 1607 34% - 0 - > AFAICS the planner will do this one just fine > without any thumb on the scales. How to I find the thumb? Ed
Ed Loehr <pggeneral@bluepolka.net> writes: > Tom Lane wrote: > Second, the same query sometimes takes 10-50 seconds shortly after > possibly a dump or other high-data-volume queries are executed, after > which it then returns to 1 second execution time. Getting crowded out > of shared memory? >> >> Sounds like it. What shared-buffers setting are you using? How much >> RAM in the box? > shared_buffers = 256 That's not a lot --- 256*8K = 2MB. You might try something in the low thousands. > RAM: 2.4GB, maybe? Not that familiar with HPUX mem setup... swapinfo won't tell you anything about physical RAM. If you poke around in SAM, I think there's some displays in there about hardware ... regards, tom lane
Tom Lane wrote: > >>Second, the same query sometimes takes 10-50 seconds shortly after >>possibly a dump or other high-data-volume queries are executed, after >>which it then returns to 1 second execution time. Getting crowded out >>of shared memory? >> >>>Sounds like it. What shared-buffers setting are you using? How much >>>RAM in the box? > >>shared_buffers = 256 > > That's not a lot --- 256*8K = 2MB. You might try something in the low > thousands. > >>RAM: 2.4GB, maybe? Not that familiar with HPUX mem setup... SAM indicates 512MB of RAM. I upped the shared buffers from 256 to 4096, and the hashjoin query came down from ~90 seconds to 10, still 10x slower than the 1-sec nested loops. Is that a performance difference you'd expect between hash and nested loops on this query because of EXISTS? Ed
Ed Loehr wrote: >> >>> Second, the same query sometimes takes 10-50 seconds shortly after >>> possibly a dump or other high-data-volume queries are executed, after >>> which it then returns to 1 second execution time. Getting crowded out >>> of shared memory? >>> >>>> Sounds like it. What shared-buffers setting are you using? How much >>>> RAM in the box? >> >>> shared_buffers = 256 >> >> That's not a lot --- 256*8K = 2MB. You might try something in the low >> thousands. > > SAM indicates 512MB of RAM. I upped the shared buffers from 256 to > 4096, and the hashjoin query came down from ~90 seconds to 10, still 10x > slower than the 1-sec nested loops. Is that a performance difference > you'd expect between hash and nested loops on this query because of EXISTS? What I neglected to mention was that the planner was *choosing* the slower hashjoin plan over the much faster nested loop plan without any PGOPTIONS set or any postgresql.conf changes to enable_*, thus the motivation for a "thumb on the scales." After upping the number of shared buffers, it has begun choosing the smart plan 1-second plan, apparently after a restart, not sure. Thanks, Tom. Ed
Has there been any work on an MDDB extension to PostgreSQL? We are moving to a MOLAP environment, and PostgreSQL is my favorite RDBMS. If no one has worked on it, I guess I will have to see what I can do... The only real obstacle is time.
Ed Loehr <pggeneral@bluepolka.net> writes: > What I neglected to mention was that the planner was *choosing* the > slower hashjoin plan over the much faster nested loop plan without any > PGOPTIONS set or any postgresql.conf changes to enable_*, thus the > motivation for a "thumb on the scales." After upping the number of > shared buffers, it has begun choosing the smart plan 1-second plan, Interesting. The estimated cost of indexscans is dependent on shared_buffers, but not so dependent that I'd have expected it to make a difference here. What were the EXPLAIN numbers you were getting, again? regards, tom lane
Hi all, I can't specify an alias for the table I want to delete from. Is this a bug or is that behavior mandated by the SQL standard? JLL
Ok.. I know its provably something im doing dumb.. but here it goes.. I have 2 tables that are the same: "temp_table" and "table" "temp _table" has 7,761 rows and "table" is empty the columns for both tables are: ID (primary key sequence), index, column1, column2 when i run: Insert Into table select index, column1, column2 from temp_table where index NOT IN (select index from table) it takes 40 MINUTES to execute.. i dont know what im doing wrong here both tables have "index" indexed .. help...
Tom Lane wrote: > >>What I neglected to mention was that the planner was *choosing* the >>slower hashjoin plan over the much faster nested loop plan without any >>PGOPTIONS set or any postgresql.conf changes to enable_*, thus the >>motivation for a "thumb on the scales." After upping the number of >>shared buffers, it has begun choosing the smart plan 1-second plan, > > Interesting. The estimated cost of indexscans is dependent on > shared_buffers, but not so dependent that I'd have expected it to make a > difference here. What were the EXPLAIN numbers you were getting, again? The default plan looked like the "-fn" plan below. I guess I should also mention there are a number of columns in the 'story' table that are not involved in the query or plans, but would add to the 'weight' of a row if that makes a difference to the planner. I omitted them from my earlier listings thinking they were superfluous to this discussion. Plan with PGOPTIONS = "-fn": Limit (cost=15409053054.71..15409053054.73 rows=1 width=12) -> Unique (cost=15409053054.71..15409053054.73 rows=1 width=12) -> Sort (cost=15409053054.71..15409053054.71 rows=9 width=12) -> Nested Loop (cost=100000000.00..15409053054.57 rows=9 width=12) -> Index Scan using word_idx on dict d1 (cost=0.00..5.98 rows=1 width=4) -> Index Scan using story_dict_pkey on story_dict ftd1 (cost=0.00..15309052993.63 rows=4398 width=8) SubPlan -> Merge Join (cost=429157.62..435130.62 rows=1 width=16) -> Sort (cost=13.11..13.11 rows=1 width=12) -> Hash Join (cost=5.98..13.10 rows=1 width=12) -> Index Scan using story_dict_tk_idx on story_dict ftd2 (cost=0.00..6.59 rows=106 width=8) -> Hash (cost=5.98..5.98 rows=1 width=4) -> Index Scan using word_idx on dict d2 (cost=0.00..5.98 rows=1 width=4) -> Sort (cost=429144.50..429144.50 rows=2389196 width=4) -> Seq Scan on story ft2 (cost=0.00..86701.96 rows=2389196 width=4) -> Merge Join (cost=429157.62..435130.62 rows=1 width=16) -> Sort (cost=13.11..13.11 rows=1 width=12) -> Hash Join (cost=5.98..13.10 rows=1 width=12) -> Index Scan using story_dict_tk_idx on story_dict ftd3 (cost=0.00..6.59 rows=106 width=8) -> Hash (cost=5.98..5.98 rows=1 width=4) -> Index Scan using word_idx on dict d3 (cost=0.00..5.98 rows=1 width=4) -> Sort (cost=429144.50..429144.50 rows=2389196 width=4) -> Seq Scan on story ft3 (cost=0.00..86701.96 rows=2389196 width=4) Plan with PGOPTIONS = "-fh": Limit (cost=635283.31..635283.33 rows=1 width=12) -> Unique (cost=635283.31..635283.33 rows=1 width=12) -> Sort (cost=635283.31..635283.31 rows=9 width=12) -> Nested Loop (cost=0.00..635283.17 rows=9 width=12) -> Index Scan using word_idx on dict d1 (cost=0.00..5.98 rows=1 width=4) -> Index Scan using story_dict_pkey on story_dict ftd1 (cost=0.00..635222.22 rows=4398 width=8) SubPlan -> Nested Loop (cost=0.00..16.07 rows=1 width=16) -> Nested Loop (cost=0.00..12.00 rows=1 width=12) -> Index Scan using word_idx on dict d2 (cost=0.00..5.98 rows=1 width=4) -> Index Scan using story_dict_pkey on story_dict ftd2 (cost=0.00..6.01 rows=1 width=8) -> Index Scan using story_pkey on story ft2 (cost=0.00..4.06 rows=1 width=4) -> Nested Loop (cost=0.00..16.07 rows=1 width=16) -> Nested Loop (cost=0.00..12.00 rows=1 width=12) -> Index Scan using word_idx on dict d3 (cost=0.00..5.98 rows=1 width=4) -> Index Scan using story_dict_pkey on story_dict ftd3 (cost=0.00..6.01 rows=1 width=8) -> Index Scan using story_pkey on story ft3 (cost=0.00..4.06 rows=1 width=4)
Jean-Luc Lachance <jllachan@nsd.ca> writes: > I can't specify an alias for the table I want to delete from. > Is this a bug or is that behavior mandated by the SQL standard? No, and yes. regards, tom lane
On Tue, 9 Apr 2002, Jean-Luc Lachance wrote: > Hi all, > > I can't specify an alias for the table I want to delete from. > > Is this a bug or is that behavior mandated by the SQL standard? Pretty sure it's at least SQL 92 spec... <delete statement: searched> seems to have the form of: DELETE FROM <table name> [WHERE <search condition>]
On Tue, 9 Apr 2002, Alexis Maldonado wrote: > Ok.. I know its provably something im doing dumb.. > but here it goes.. > > I have 2 tables that are the same: > > "temp_table" and "table" > > "temp _table" has 7,761 rows and "table" is empty > > the columns for both tables are: ID (primary key sequence), index, column1, > column2 > > when i run: > > Insert Into table > select index, column1, column2 > from temp_table > where index NOT IN (select index from table) IN is unfortunately implemented slowly (I think the FAQ answer has more details) You can often get better performance using exists, I think the equivalent would be: insert into table select index, column1, column2 from temp_table where NOT EXISTS (select * from table where table.index=temp_Table.index)
thanks i'll try that :) ----- Original Message ----- From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com> To: "Alexis Maldonado" <amaldona@ctcd.cc.tx.us> Cc: <pgsql-general@postgresql.org> Sent: Tuesday, April 09, 2002 3:56 PM Subject: Re: [GENERAL] Sub-selects taking way too long.. > On Tue, 9 Apr 2002, Alexis Maldonado wrote: > > > Ok.. I know its provably something im doing dumb.. > > but here it goes.. > > > > I have 2 tables that are the same: > > > > "temp_table" and "table" > > > > "temp _table" has 7,761 rows and "table" is empty > > > > the columns for both tables are: ID (primary key sequence), index, column1, > > column2 > > > > when i run: > > > > Insert Into table > > select index, column1, column2 > > from temp_table > > where index NOT IN (select index from table) > > IN is unfortunately implemented slowly (I think the FAQ answer has more > details) > > You can often get better performance using exists, I think the equivalent > would be: > insert into table > select index, column1, column2 from temp_table > where NOT EXISTS (select * from table where table.index=temp_Table.index) > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
cool.. used the EXISTS and now it does it in 3 seconds instead of 40 minutes.. wow.. heheh ThanX!! hehe ----- Original Message ----- From: "Alexis Maldonado" <amaldona@ctcd.cc.tx.us> To: <pgsql-general@postgresql.org> Sent: Tuesday, April 09, 2002 4:09 PM Subject: Re: [GENERAL] Sub-selects taking way too long.. > thanks i'll try that :) > > ----- Original Message ----- > From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com> > To: "Alexis Maldonado" <amaldona@ctcd.cc.tx.us> > Cc: <pgsql-general@postgresql.org> > Sent: Tuesday, April 09, 2002 3:56 PM > Subject: Re: [GENERAL] Sub-selects taking way too long.. > > > > On Tue, 9 Apr 2002, Alexis Maldonado wrote: > > > > > Ok.. I know its provably something im doing dumb.. > > > but here it goes.. > > > > > > I have 2 tables that are the same: > > > > > > "temp_table" and "table" > > > > > > "temp _table" has 7,761 rows and "table" is empty > > > > > > the columns for both tables are: ID (primary key sequence), index, > column1, > > > column2 > > > > > > when i run: > > > > > > Insert Into table > > > select index, column1, column2 > > > from temp_table > > > where index NOT IN (select index from table) > > > > IN is unfortunately implemented slowly (I think the FAQ answer has more > > details) > > > > You can often get better performance using exists, I think the equivalent > > would be: > > insert into table > > select index, column1, column2 from temp_table > > where NOT EXISTS (select * from table where table.index=temp_Table.index) > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >