Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 6564B6338DB for ; Wed, 10 Feb 2010 21:52:48 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 89683-03-2 for ; Thu, 11 Feb 2010 01:52:29 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from out-0.mx.aerioconnect.net (out-0-6.mx.aerioconnect.net [216.240.47.66]) by mail.postgresql.org (Postfix) with ESMTP id 8F10D633770 for ; Wed, 10 Feb 2010 21:52:36 -0400 (AST) Received: from idiom.com (postfix@mx0.idiom.com [216.240.32.160]) by out-0.mx.aerioconnect.net (8.13.8/8.13.8) with ESMTP id o1B1qTTY013986; Wed, 10 Feb 2010 17:52:29 -0800 X-Client-Authorized: MaGic Cook1e X-Client-Authorized: MaGic Cook1e X-Client-Authorized: MaGic Cook1e X-Client-Authorized: MaGic Cook1e Received: from [192.168.2.113] (adsl-71-135-173-32.dsl.pltn13.pacbell.net [71.135.173.32]) by idiom.com (Postfix) with ESMTP id C0F022D6019; Wed, 10 Feb 2010 17:52:28 -0800 (PST) Message-ID: <4B7362DC.2060301@obviously.com> Date: Wed, 10 Feb 2010 17:52:28 -0800 From: Bryce Nesbitt User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1.23) Gecko/20090410 SUSE/1.1.18-0.1 SeaMonkey/1.1.18 MIME-Version: 1.0 To: pgsql-performance@postgresql.org CC: Robert Haas , Pavel Stehule , "Jorge Montero" Subject: Re: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk? References: <4B723042.7090406@obviously.com> <4B726E81.2060904@obviously.com> <603c8f071002101231k45e9161eyeacf9072219489eb@mail.gmail.com> In-Reply-To: <603c8f071002101231k45e9161eyeacf9072219489eb@mail.gmail.com> Content-Type: multipart/mixed; boundary="------------070804020306020208050000" X-Scanned-By: MIMEDefang 2.67 on 216.240.47.51 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.805 tagged_above=-10 required=5 tests=AWL=0.793, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/176 X-Sequence-Number: 37597 This is a multi-part message in MIME format. --------------070804020306020208050000 Content-Type: multipart/alternative; boundary="------------050508020701010906020409" --------------050508020701010906020409 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit If you guys succeed in making this class of query perform, you'll have beat out the professional consulting firm we hired, which was all but useless! The query is usually slow, but particular combinations of words seem to make it obscenely slow. The query plans are now attached (sorry I did not start there: many lists reject attachments). Or you can click on "text" at the query planner analysis site http://explain.depesz.com/s/qYq _Here's typical server load:_ Tasks: 166 total, 1 running, 165 sleeping, 0 stopped, 0 zombie Cpu(s): 1.2%us, 0.9%sy, 0.0%ni, 86.5%id, 11.2%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 32966936k total, 32873860k used, 93076k free, 2080k buffers Swap: 33554424k total, 472k used, 33553952k free, 30572904k cached _ Configurations modified from Postgres 8.3 default are:_ listen_addresses = '10.100.2.11, 10.101.2.11' # what IP address(es) to listen on; port = 5432 # (change requires restart) max_connections = 400 # (change requires restart) shared_buffers = 4096MB # min 128kB or max_connections*16kB work_mem = 16MB # min 64kB max_fsm_pages = 500000 # default:20000 min:max_fsm_relations*16,6 bytes see:MAIN-5740 max_fsm_relations = 2700 # min 100, ~70 bytes each checkpoint_segments = 20 # in logfile segments, min 1, 16MB each random_page_cost = 2.0 # same scale as above effective_cache_size = 28672MB default_statistics_target = 150 # range 1-1000 log_destination = 'syslog' # Valid values are combinations of log_min_error_statement = error # values in order of decreasing detail: log_min_duration_statement = 5000 # -1 is disabled, 0 logs all statements log_checkpoints = on # default off autovacuum_naptime = 5min # time between autovacuum runs escape_string_warning = off # default:on (See bepress MAIN-4857) standard_conforming_strings = off # deafult:off (See bepress MAIN-4857) production=# EXPLAIN ANALYZE SELECT context_key FROM article_words JOIN words using (word_key) WHERE word = 'insider'; ------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..23393.15 rows=23 width=4) (actual time=0.077..15.637 rows=4003 loops=1) -> Index Scan using words_word on words (cost=0.00..5.47 rows=1 width=4) (actual time=0.049..0.051 rows=1 loops=1) Index Cond: ((word)::text = 'insider'::text) -> Index Scan using article_words_wc on article_words (cost=0.00..23234.38 rows=12264 width=8) (actual time=0.020..7.237 rows=4003 loops=1) Index Cond: (article_words.word_key = words.word_key) Total runtime: 19.776 ms production=# EXPLAIN ANALYZE SELECT context_key FROM article_words WHERE word_key = 3675; ------------------------------------------------------------------------------------------------------------------------------------------- Index Scan using article_words_wc on article_words (cost=0.00..21433.53 rows=11309 width=4) (actual time=0.025..7.579 rows=4003 loops=1) Index Cond: (word_key = 3675) Total runtime: 11.704 ms production=# explain analyze select count(*) from article_words; Aggregate (cost=263831.63..263831.64 rows=1 width=0) (actual time=35851.654..35851.655 rows=1 loops=1) -> Seq Scan on words (cost=0.00..229311.30 rows=13808130 width=0) (actual time=0.043..21281.124 rows=13808184 loops=1) Total runtime: 35851.723 ms production=# select count(*) from words; 13,808,184 production=# explain analyze select count(*) from article_words; Aggregate (cost=5453242.40..5453242.41 rows=1 width=0) (actual time=776504.017..776504.018 rows=1 loops=1) -> Seq Scan on article_words (cost=0.00..4653453.52 rows=319915552 width=0) (actual time=0.034..438969.347 rows=319956663 loops=1) Total runtime: 776504.177 ms production=# select count(*) from article_words; 319,956,720 --------------050508020701010906020409 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit If you guys succeed in making this class of query perform, you'll have beat out the professional consulting firm we hired, which was all but useless!  The query is usually slow, but particular combinations of words seem to make it obscenely slow.

The query plans are now attached (sorry I did not start there: many lists reject attachments). Or you can click on "text" at the query planner analysis site http://explain.depesz.com/s/qYq


Here's typical server load:
Tasks: 166 total,   1 running, 165 sleeping,   0 stopped,   0 zombie
Cpu(s):  1.2%us,  0.9%sy,  0.0%ni, 86.5%id, 11.2%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  32966936k total, 32873860k used,    93076k free,     2080k buffers
Swap: 33554424k total,      472k used, 33553952k free, 30572904k cached


Configurations modified from Postgres 8.3 default are:
listen_addresses = '10.100.2.11, 10.101.2.11'   # what IP address(es) to listen on;
port = 5432                             # (change requires restart)
max_connections = 400                   # (change requires restart)
shared_buffers = 4096MB                 # min 128kB or max_connections*16kB
work_mem = 16MB                         # min 64kB
max_fsm_pages =  500000                 # default:20000 min:max_fsm_relations*16,6 bytes see:MAIN-5740
max_fsm_relations = 2700                # min 100, ~70 bytes each
checkpoint_segments = 20                # in logfile segments, min 1, 16MB each
random_page_cost = 2.0                  # same scale as above
effective_cache_size = 28672MB
default_statistics_target = 150         # range 1-1000
log_destination = 'syslog'              # Valid values are combinations of
log_min_error_statement = error # values in order of decreasing detail:
log_min_duration_statement = 5000       # -1 is disabled, 0 logs all statements
log_checkpoints = on                    # default off
autovacuum_naptime = 5min               # time between autovacuum runs
escape_string_warning = off             # default:on  (See bepress MAIN-4857)
standard_conforming_strings = off       # deafult:off (See bepress MAIN-4857)



production=# EXPLAIN ANALYZE SELECT context_key FROM article_words
JOIN words using (word_key) WHERE word = 'insider';
-------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..23393.15 rows=23 width=4) (actual time=0.077..15.637 rows=4003 loops=1)
   ->  Index Scan using words_word on words  (cost=0.00..5.47 rows=1 width=4) (actual time=0.049..0.051 rows=1 loops=1)
         Index Cond: ((word)::text = 'insider'::text)
   ->  Index Scan using article_words_wc on article_words  (cost=0.00..23234.38 rows=12264 width=8) (actual time=0.020..7.237 rows=4003 loops=1)
         Index Cond: (article_words.word_key = words.word_key)
 Total runtime: 19.776 ms

production=# EXPLAIN ANALYZE SELECT context_key FROM article_words
WHERE word_key = 3675;
-------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using article_words_wc on article_words  (cost=0.00..21433.53 rows=11309 width=4) (actual time=0.025..7.579 rows=4003 loops=1)
   Index Cond: (word_key = 3675)
 Total runtime: 11.704 ms



production=# explain analyze select count(*) from article_words;
Aggregate  (cost=263831.63..263831.64 rows=1 width=0) (actual time=35851.654..35851.655 rows=1 loops=1)
   ->  Seq Scan on words  (cost=0.00..229311.30 rows=13808130 width=0) (actual time=0.043..21281.124 rows=13808184 loops=1)
 Total runtime: 35851.723 ms

production=# select count(*) from words;
13,808,184


production=# explain analyze select count(*) from article_words;
Aggregate  (cost=5453242.40..5453242.41 rows=1 width=0) (actual time=776504.017..776504.018 rows=1 loops=1)
   ->  Seq Scan on article_words  (cost=0.00..4653453.52 rows=319915552 width=0) (actual time=0.034..438969.347 rows=319956663 loops=1)
 Total runtime: 776504.177 ms

production=# select count(*) from article_words;
319,956,720

--------------050508020701010906020409-- --------------070804020306020208050000 Content-Type: text/plain; name="pgsql_explain1.txt" Content-Transfer-Encoding: base64 Content-Disposition: inline; filename="pgsql_explain1.txt" QWdncmVnYXRlICAoY29zdD0xMzkyNzkuODUuLjEzOTI3OS44NiByb3dzPTEgd2lkdGg9NCkg KGFjdHVhbCB0aW1lPTUwMjQ5My4wMDAuLjUwMjQ5My4wMDEgcm93cz0xIGxvb3BzPTEpCiAg IC0+ICBIYXNoIEpvaW4gIChjb3N0PTEzMTMxNC4zMS4uMTM5Mjc5Ljg0IHJvd3M9MiB3aWR0 aD00KSAoYWN0dWFsIHRpbWU9NTAxNzg3LjM5Ny4uNTAyNDkyLjMxNiByb3dzPTYyMiBsb29w cz0xKQogICAgICAgICBIYXNoIENvbmQ6IChtYXR2aWV3XzgyMDM0LmNvbnRleHRfa2V5ID0g YXJ0aWNsZXMuY29udGV4dF9rZXkpCiAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiBtYXR2aWV3 XzgyMDM0ICAoY29zdD0wLjAwLi42MzIyLjIwIHJvd3M9NDM4MjIwIHdpZHRoPTQpIChhY3R1 YWwgdGltZT0wLjAxNC4uNDYyLjMxMiByb3dzPTQzODIyMCBsb29wcz0xKQogICAgICAgICAt PiAgSGFzaCAgKGNvc3Q9MTMxMzE0LjMwLi4xMzEzMTQuMzAgcm93cz0xIHdpZHRoPTE2KSAo YWN0dWFsIHRpbWU9NTAxNTUzLjc1NS4uNTAxNTUzLjc1NSByb3dzPTYyMiBsb29wcz0xKQog ICAgICAgICAgICAgICAtPiAgTmVzdGVkIExvb3AgSU4gSm9pbiAgKGNvc3Q9NDYyOTEuNzku LjEzMTMxNC4zMCByb3dzPTEgd2lkdGg9MTYpIChhY3R1YWwgdGltZT00NjcuNTQ2Li41MDE1 NTAuNzM1IHJvd3M9NjIyIGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgIEpvaW4gRmls dGVyOiAoYS5jb250ZXh0X2tleSA9IGFydGljbGVzLmNvbnRleHRfa2V5KQogICAgICAgICAg ICAgICAgICAgICAtPiAgTmVzdGVkIExvb3AgIChjb3N0PTQ2MjkxLjc5Li40NjMyMy4xNSBy b3dzPTIgd2lkdGg9MTIpIChhY3R1YWwgdGltZT0xNzkuNzYwLi4zMDMuNDc0IHJvd3M9MTQ3 MyBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgTmVzdGVkIExvb3Ag IChjb3N0PTQ2MjkxLjc5Li40NjMxNC4zNSByb3dzPTIgd2lkdGg9OCkgKGFjdHVhbCB0aW1l PTE3OS43NDMuLjI3My44NjYgcm93cz0xNDczIGxvb3BzPTEpCiAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgIC0+ICBTdWJxdWVyeSBTY2FuICJJTl9zdWJxdWVyeSIgIChjb3N0 PTQ2MjkxLjc5Li40NjI5Mi4wNiByb3dzPTUgd2lkdGg9NCkgKGFjdHVhbCB0aW1lPTE3OS43 MTUuLjIzMy45NDIgcm93cz0xNjUzIGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgIC0+ICBTZXRPcCBJbnRlcnNlY3QgIChjb3N0PTQ2MjkxLjc5Li40 NjI5Mi4wMiByb3dzPTUgd2lkdGg9NCkgKGFjdHVhbCB0aW1lPTE3OS43MTIuLjIyOS4wMzAg cm93cz0xNjUzIGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgIC0+ICBTb3J0ICAoY29zdD00NjI5MS43OS4uNDYyOTEuOTAgcm93cz00NiB3 aWR0aD00KSAoYWN0dWFsIHRpbWU9MTc5LjY5NS4uMjAxLjA2OCByb3dzPTE5NTI5IGxvb3Bz PTEpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg IFNvcnQgS2V5OiAiKlNFTEVDVCogMSIuY29udGV4dF9rZXkKICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgU29ydCBNZXRob2Q6ICBxdWlja3Nv cnQgIE1lbW9yeTogMTY4NGtCCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgIC0+ICBBcHBlbmQgIChjb3N0PTAuMDAuLjQ2MjkwLjUyIHJvd3M9 NDYgd2lkdGg9NCkgKGFjdHVhbCB0aW1lPTAuMDY1Li4xNTAuNjQ0IHJvd3M9MTk1MjkgbG9v cHM9MSkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgLT4gIFN1YnF1ZXJ5IFNjYW4gIipTRUxFQ1QqIDEiICAoY29zdD0wLjAwLi4y MzE0NS4yNiByb3dzPTIzIHdpZHRoPTQpIChhY3R1YWwgdGltZT0wLjA2My4uMjIuNjQzIHJv d3M9NDAwMyBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgTmVzdGVkIExvb3AgIChjb3N0PTAuMDAu LjIzMTQ1LjAzIHJvd3M9MjMgd2lkdGg9NCkgKGFjdHVhbCB0aW1lPTAuMDYxLi4xNC43NDAg cm93cz00MDAzIGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5n IHdvcmRzX3dvcmQgb24gd29yZHMgIChjb3N0PTAuMDAuLjUuNDcgcm93cz0xIHdpZHRoPTQp IChhY3R1YWwgdGltZT0wLjAzNy4uMC4wMzkgcm93cz0xIGxvb3BzPTEpCiAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgIEluZGV4IENvbmQ6ICgod29yZCk6OnRleHQgPSAnaW5zaWRlcic6OnRleHQp CiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIGFydGljbGVfd29yZHNfd2Mg b24gYXJ0aWNsZV93b3JkcyAgKGNvc3Q9MC4wMC4uMjI5ODcuODYgcm93cz0xMjEzNiB3aWR0 aD04KSAoYWN0dWFsIHRpbWU9MC4wMTcuLjYuNzU0IHJvd3M9NDAwMyBsb29wcz0xKQogICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICBJbmRleCBDb25kOiAocHVibGljLmFydGljbGVfd29yZHMud29y ZF9rZXkgPSBwdWJsaWMud29yZHMud29yZF9rZXkpCiAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBTdWJxdWVyeSBTY2FuICIq U0VMRUNUKiAyIiAgKGNvc3Q9MC4wMC4uMjMxNDUuMjYgcm93cz0yMyB3aWR0aD00KSAoYWN0 dWFsIHRpbWU9MC4wNDUuLjg4LjA4MyByb3dzPTE1NTI2IGxvb3BzPTEpCiAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+ ICBOZXN0ZWQgTG9vcCAgKGNvc3Q9MC4wMC4uMjMxNDUuMDMgcm93cz0yMyB3aWR0aD00KSAo YWN0dWFsIHRpbWU9MC4wNDIuLjU2Ljk1NiByb3dzPTE1NTI2IGxvb3BzPTEpCiAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIHdvcmRzX3dvcmQgb24gd29yZHMgIChjb3N0 PTAuMDAuLjUuNDcgcm93cz0xIHdpZHRoPTQpIChhY3R1YWwgdGltZT0wLjAyNC4uMC4wMjcg cm93cz0xIGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6ICgod29y ZCk6OnRleHQgPSAndHJhZGluZyc6OnRleHQpCiAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBJbmRleCBT Y2FuIHVzaW5nIGFydGljbGVfd29yZHNfd2Mgb24gYXJ0aWNsZV93b3JkcyAgKGNvc3Q9MC4w MC4uMjI5ODcuODYgcm93cz0xMjEzNiB3aWR0aD04KSAoYWN0dWFsIHRpbWU9MC4wMTMuLjI2 LjA0MyByb3dzPTE1NTI2IGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENv bmQ6IChwdWJsaWMuYXJ0aWNsZV93b3Jkcy53b3JkX2tleSA9IHB1YmxpYy53b3Jkcy53b3Jk X2tleSkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4g dXNpbmcgYXJ0aWNsZV9rZXlfaWR4IG9uIGFydGljbGVzICAoY29zdD0wLjAwLi40LjQ0IHJv d3M9MSB3aWR0aD00KSAoYWN0dWFsIHRpbWU9MC4wMTYuLjAuMDE5IHJvd3M9MSBsb29wcz0x NjUzKQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBJbmRleCBDb25k OiAoYXJ0aWNsZXMuY29udGV4dF9rZXkgPSAiSU5fc3VicXVlcnkiLmNvbnRleHRfa2V5KQog ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBGaWx0ZXI6IGFydGljbGVz LmluZGV4ZWQKICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNp bmcgY29udGV4dHNfcGtleSBvbiBjb250ZXh0cyAgKGNvc3Q9MC4wMC4uNC4zOSByb3dzPTEg d2lkdGg9NCkgKGFjdHVhbCB0aW1lPTAuMDExLi4wLjAxNCByb3dzPTEgbG9vcHM9MTQ3MykK ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgSW5kZXggQ29uZDogKGNvbnRleHRz LmNvbnRleHRfa2V5ID0gYXJ0aWNsZXMuY29udGV4dF9rZXkpCiAgICAgICAgICAgICAgICAg ICAgIC0+ICBOZXN0ZWQgTG9vcCAgKGNvc3Q9MC4wMC4uMTExNjYwLjQyIHJvd3M9MTI2NjEw NCB3aWR0aD00KSAoYWN0dWFsIHRpbWU9MC4wMTguLjMwMC4wMzYgcm93cz0zOTIwMSBsb29w cz0xNDczKQogICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgU2VxIFNjYW4gb24gYnBf Y2F0ZWdvcmllcyAgKGNvc3Q9MC4wMC4uMTMxNS41OSByb3dzPTE2NjQ1IHdpZHRoPTQpIChh Y3R1YWwgdGltZT0wLjAwOC4uNTcuNDQwIHJvd3M9MTQ1MzAgbG9vcHM9MTQ3MykKICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgRmlsdGVyOiAobG93ZXIoY2F0ZWdvcnkpID0g J2xhdyc6OnRleHQpCiAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2Fu IHVzaW5nIHZpcnR1YWxfYW5jZXN0b3Jfa2V5X2lkeCBvbiB2aXJ0dWFsX2FuY2VzdG9ycyBh ICAoY29zdD0wLjAwLi41LjE4IHJvd3M9MTE2IHdpZHRoPTgpIChhY3R1YWwgdGltZT0wLjAw NS4uMC4wMDkgcm93cz0zIGxvb3BzPTIxNDAyOTQyKQogICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICBJbmRleCBDb25kOiAoYS5hbmNlc3Rvcl9rZXkgPSBicF9jYXRlZ29yaWVz LmNvbnRleHRfa2V5KQogVG90YWwgcnVudGltZTogNTAyNDkzLjM4MyBtcwo= --------------070804020306020208050000 Content-Type: text/plain; name="pgsql_explain2.txt" Content-Transfer-Encoding: base64 Content-Disposition: inline; filename="pgsql_explain2.txt" IE5lc3RlZCBMb29wICAoY29zdD0xODAxOTAuNTQuLjE4MDIyMS42OCByb3dzPTIgd2lkdGg9 NCkgKGFjdHVhbCB0aW1lPTczMDAuMDgxLi43MzE3LjUwOCByb3dzPTYyMiBsb29wcz0xKQog ICAtPiAgTmVzdGVkIExvb3AgIChjb3N0PTE4MDE5MC41NC4uMTgwMjEyLjg4IHJvd3M9MiB3 aWR0aD0yMCkgKGFjdHVhbCB0aW1lPTczMDAuMDYxLi43MzA5Ljk4OSByb3dzPTYyMiBsb29w cz0xKQogICAgICAgICAtPiAgSGFzaEFnZ3JlZ2F0ZSAgKGNvc3Q9MTgwMTkwLjU0Li4xODAx OTAuNTkgcm93cz01IHdpZHRoPTE2KSAoYWN0dWFsIHRpbWU9NzMwMC4wMTcuLjczMDAuOTU5 IHJvd3M9NzI2IGxvb3BzPTEpCiAgICAgICAgICAgICAgIC0+ICBIYXNoIElOIEpvaW4gIChj b3N0PTE2NzI3My40OS4uMTgwMTkwLjUzIHJvd3M9NSB3aWR0aD0xNikgKGFjdHVhbCB0aW1l PTY1ODQuOTQ4Li43Mjk4Ljg0OSByb3dzPTcyNiBsb29wcz0xKQogICAgICAgICAgICAgICAg ICAgICBIYXNoIENvbmQ6IChwdWJsaWMuY29udGV4dHMuY29udGV4dF9rZXkgPSBhLmNvbnRl eHRfa2V5KQogICAgICAgICAgICAgICAgICAgICAtPiAgSGFzaCBKb2luICAoY29zdD00Njgx MC4zOC4uNTQ3NzUuOTMgcm93cz0yIHdpZHRoPTEyKSAoYWN0dWFsIHRpbWU9MjQ2LjY5MS4u MTE3Ni4yNDEgcm93cz0xNjM1IGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAgICAg IEhhc2ggQ29uZDogKG1hdHZpZXdfODIwMzQuY29udGV4dF9rZXkgPSBwdWJsaWMuY29udGV4 dHMuY29udGV4dF9rZXkpCiAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2Nh biBvbiBtYXR2aWV3XzgyMDM0ICAoY29zdD0wLjAwLi42MzIyLjIwIHJvd3M9NDM4MjIwIHdp ZHRoPTQpIChhY3R1YWwgdGltZT0wLjAxNS4uNDQ4LjYwNyByb3dzPTQzODIyMCBsb29wcz0x KQogICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgSGFzaCAgKGNvc3Q9NDY4MTAuMzIu LjQ2ODEwLjMyIHJvd3M9NSB3aWR0aD04KSAoYWN0dWFsIHRpbWU9MjQ0LjkxMi4uMjQ0Ljkx MiByb3dzPTE2NTMgbG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg LT4gIE5lc3RlZCBMb29wICAoY29zdD00Njc4OC4wMy4uNDY4MTAuMzIgcm93cz01IHdpZHRo PTgpIChhY3R1YWwgdGltZT0xNzcuMjE2Li4yNDIuNzcxIHJvd3M9MTY1MyBsb29wcz0xKQog ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgU3VicXVlcnkgU2Nh biAiSU5fc3VicXVlcnkiICAoY29zdD00Njc4OC4wMy4uNDY3ODguMzEgcm93cz01IHdpZHRo PTQpIChhY3R1YWwgdGltZT0xNzcuMTg5Li4yMjMuOTY1IHJvd3M9MTY1MyBsb29wcz0xKQog ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgU2V0T3Ag SW50ZXJzZWN0ICAoY29zdD00Njc4OC4wMy4uNDY3ODguMjYgcm93cz01IHdpZHRoPTQpIChh Y3R1YWwgdGltZT0xNzcuMTg1Li4yMjAuNzE3IHJvd3M9MTY1MyBsb29wcz0xKQogICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgU29ydCAg KGNvc3Q9NDY3ODguMDMuLjQ2Nzg4LjE1IHJvd3M9NDYgd2lkdGg9NCkgKGFjdHVhbCB0aW1l PTE3Ny4xNjcuLjE5Ni42Mzggcm93cz0xOTUzMCBsb29wcz0xKQogICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBTb3J0IEtleTogIipT RUxFQ1QqIDEiLmNvbnRleHRfa2V5CiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgIFNvcnQgTWV0aG9kOiAgcXVpY2tzb3J0ICBNZW1v cnk6IDE2ODRrQgogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAtPiAgQXBwZW5kICAoY29zdD0wLjAwLi40Njc4Ni43NiByb3dzPTQ2 IHdpZHRoPTQpIChhY3R1YWwgdGltZT0wLjA3MC4uMTQ4LjIzNCByb3dzPTE5NTMwIGxvb3Bz PTEpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgIC0+ICBTdWJxdWVyeSBTY2FuICIqU0VMRUNUKiAxIiAgKGNvc3Q9MC4w MC4uMjMzOTMuMzggcm93cz0yMyB3aWR0aD00KSAoYWN0dWFsIHRpbWU9MC4wNjguLjIzLjI2 NSByb3dzPSAgICA0MDAzIGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBOZXN0ZWQgTG9v cCAgKGNvc3Q9MC4wMC4uMjMzOTMuMTUgcm93cz0yMyB3aWR0aD00KSAoYWN0dWFsIHRpbWU9 MC4wNjYuLjE1LjA2MSByb3dzPTQwMDMgbG9vcCAgICBzPTEpCiAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIHdvcmRzX3dvcmQgb24gd29yZHMgIChjb3N0PTAu MDAuLjUuNDcgcm93cz0xIHdpZHRoPTQpIChhY3R1YWwgdGltZT0wLiAgICAwNDAuLjAuMDQz IHJvd3M9MSBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBJbmRleCBDb25k OiAoKHdvcmQpOjp0ZXh0ID0gJ2luc2lkZXInOjp0ZXh0KQogICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAtPiAgSW5kZXggU2NhbiB1c2luZyBhcnRpY2xlX3dvcmRzX3djIG9uIGFydGljbGVfd29y ZHMgIChjb3N0PTAuMDAuLjIzMjM0LjM4IHJvd3M9MTIyNjQgd2kgICAgZHRoPTgpIChhY3R1 YWwgdGltZT0wLjAxOS4uNy4wNjUgcm93cz00MDAzIGxvb3BzPTEpCiAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgIEluZGV4IENvbmQ6IChwdWJsaWMuYXJ0aWNsZV93b3Jkcy53b3JkX2tl eSA9IHB1YmxpYy53b3Jkcy53b3JkX2tleSkKICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIFN1YnF1ZXJ5IFNjYW4g IipTRUxFQ1QqIDIiICAoY29zdD0wLjAwLi4yMzM5My4zOCByb3dzPTIzIHdpZHRoPTQpIChh Y3R1YWwgdGltZT0wLjA1OS4uODcuNjE0IHJvd3M9ICAgIDE1NTI3IGxvb3BzPTEpCiAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgIC0+ICBOZXN0ZWQgTG9vcCAgKGNvc3Q9MC4wMC4uMjMzOTMuMTUgcm93cz0y MyB3aWR0aD00KSAoYWN0dWFsIHRpbWU9MC4wNTUuLjU2Ljk4MiByb3dzPTE1NTI3IGxvbyAg ICBwcz0xKQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgSW5kZXggU2NhbiB1c2luZyB3b3Jk c193b3JkIG9uIHdvcmRzICAoY29zdD0wLjAwLi41LjQ3IHJvd3M9MSB3aWR0aD00KSAoYWN0 dWFsIHRpbWU9MC4gICAgMDMyLi4wLjAzNiByb3dzPTEgbG9vcHM9MSkKICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgSW5kZXggQ29uZDogKCh3b3JkKTo6dGV4dCA9ICd0cmFkaW5nJzo6 dGV4dCkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgYXJ0aWNs ZV93b3Jkc193YyBvbiBhcnRpY2xlX3dvcmRzICAoY29zdD0wLjAwLi4yMzIzNC4zOCByb3dz PTEyMjY0IHdpICAgIGR0aD04KSAoYWN0dWFsIHRpbWU9MC4wMTYuLjI2LjE0NiByb3dzPTE1 NTI3IGxvb3BzPTEpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6IChw dWJsaWMuYXJ0aWNsZV93b3Jkcy53b3JkX2tleSA9IHB1YmxpYy53b3Jkcy53b3JkX2tleSkK ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4g dXNpbmcgY29udGV4dHNfcGtleSBvbiBjb250ZXh0cyAgKGNvc3Q9MC4wMC4uNC4zOSByb3dz PTEgd2lkdGg9NCkgKGFjdHVhbCB0aW1lPTAuMDA2Li4wLjAwNyByb3dzPTEgbG9vcHM9MTY1 MykKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgSW5kZXgg Q29uZDogKHB1YmxpYy5jb250ZXh0cy5jb250ZXh0X2tleSA9ICJJTl9zdWJxdWVyeSIuY29u dGV4dF9rZXkpCiAgICAgICAgICAgICAgICAgICAgIC0+ICBIYXNoICAoY29zdD05OTY4MC4y OC4uOTk2ODAuMjggcm93cz0xMjY2NzA2IHdpZHRoPTQpIChhY3R1YWwgdGltZT02MTA3LjEy NS4uNjEwNy4xMjUgcm93cz00ODcwMSBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgICAg ICAgICAtPiAgTWVyZ2UgSm9pbiAgKGNvc3Q9MTcxMjYuMjcuLjk5NjgwLjI4IHJvd3M9MTI2 NjcwNiB3aWR0aD00KSAoYWN0dWFsIHRpbWU9MTEzMC4yNjUuLjYwNDEuODc0IHJvd3M9NDg3 MDEgbG9vcHM9MSkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgTWVyZ2UgQ29u ZDogKGJwX2NhdGVnb3JpZXMuY29udGV4dF9rZXkgPSBhLmFuY2VzdG9yX2tleSkKICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgYnBfY2F0 ZWdvcmllc19jb250ZXh0X2tleV9pZHggb24gYnBfY2F0ZWdvcmllcyAgKGNvc3Q9MC4wMC4u MjU0My40NiByb3dzPTE2NjI1IHdpZHRoPTQpIChhY3R1YWwgdGltZT0wLjAyNy4uMTAyICAg IC40NjQgcm93cz0xNjY3MCBsb29wcz0xKQogICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICBGaWx0ZXI6IChsb3dlcihjYXRlZ29yeSkgPSAnbGF3Jzo6dGV4dCkKICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgdmly dHVhbF9hbmNlc3Rvcl9rZXlfaWR4IG9uIHZpcnR1YWxfYW5jZXN0b3JzIGEgIChjb3N0PTAu MDAuLjc5MTU5LjY1IHJvd3M9MjI5NzUwMiB3aWR0aD04KSAoYWN0dWFsIHRpbWU9MC4xMDUu ICAgIC4zNDAwLjg5MSByb3dzPTIyODY4OTAgbG9vcHM9MSkKICAgICAgICAgLT4gIEluZGV4 IFNjYW4gdXNpbmcgYXJ0aWNsZV9rZXlfaWR4IG9uIGFydGljbGVzICAoY29zdD0wLjAwLi40 LjQ0IHJvd3M9MSB3aWR0aD00KSAoYWN0dWFsIHRpbWU9MC4wMDguLjAuMDA5IHJvd3M9MSBs b29wcz03MjYpCiAgICAgICAgICAgICAgIEluZGV4IENvbmQ6IChhcnRpY2xlcy5jb250ZXh0 X2tleSA9IHB1YmxpYy5jb250ZXh0cy5jb250ZXh0X2tleSkKICAgICAgICAgICAgICAgRmls dGVyOiBhcnRpY2xlcy5pbmRleGVkCiAgIC0+ICBJbmRleCBTY2FuIHVzaW5nIGNvbnRleHRz X3BrZXkgb24gY29udGV4dHMgIChjb3N0PTAuMDAuLjQuMzkgcm93cz0xIHdpZHRoPTQpIChh Y3R1YWwgdGltZT0wLjAwNy4uMC4wMDggcm93cz0xIGxvb3BzPTYyMikKICAgICAgICAgSW5k ZXggQ29uZDogKHB1YmxpYy5jb250ZXh0cy5jb250ZXh0X2tleSA9IGFydGljbGVzLmNvbnRl eHRfa2V5KQogVG90YWwgcnVudGltZTogNzMxOC42MTggbXMK --------------070804020306020208050000--