Issues on 9.5 to 9.6 Upgrade.

From: <bend(at)linux4ms(dot)net>
To: "Postgres General" <pgsql-general(at)postgresql(dot)org>
Subject: Issues on 9.5 to 9.6 Upgrade.
Date: 2018-06-13 16:16:57
Message-ID: 20180613091657.2b8d81ae03f6297615f26284cd7372ff.fa0f73e131.wbe@email18.godaddy.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>Servers: Redhack 6.9 64 bit.</div><div>Postgres OLD Server version 9.5.6</div><div>Postgres New Server 9.6.6</div><div><br></div><div>Issues BIG difference in speed on the following SQL statement from 9.5 to 9.6 ...<br></div><div><br></div><div>EXPLAIN ANALYZE<br>select de_caseid, de_seqno, de_document_num, doc_link.dm_id as dm_id,<br>&nbsp;&nbsp; doc_link.dm_seq as dm_seq, doc_link.dm_dls_id as dm_dls_id,<br>&nbsp;&nbsp; doc_link.dm_restrict_view as dm_restrict_view, document_v.dm_type as dm_type,<br>&nbsp;&nbsp; document_v.dm_id as accessable<br>from dktentry left outer join doc_link on de_doc_id = doc_link.dm_id<br>&nbsp;&nbsp;&nbsp;&nbsp; left outer join document_v on doc_link.dm_id = document_v.dm_id</div><div>&nbsp;&nbsp;&nbsp;&nbsp; and doc_link.dm_seq = document_v.dm_seq where de_caseid in (800)<br>;<br></div><div><br></div><div>Number of rows in table dktentry:</div><div>psql -x -c "select count(*) from dktentry;"<br>-[ RECORD 1 ]-<br>count | 376555</div><div>Number of Rows in table document_v:</div><div>&nbsp;psql -x -c "select count(*) from document_v;"<br>-[ RECORD 1 ]-<br>count | 931306</div><div>Number of rows in table doc_link:</div><div>&nbsp;psql -x -c "select count(*) from doc_link;"<br>-[ RECORD 1 ]--<br>count | 1329305<br><br></div><div><br></div><div>We are migrating our current 9.5 batch of Postgres servers to a new batch of Postgres 9.6 servers.</div><div>We have tried using both pd_dump and pg dumpall to move the files across.</div><div><br></div><div>However , when executing the above statement on the 9.5 version, we get the following:</div><div>************* 9.5 Postgres<br></div><div>[postgres(at)sudb114 admintools]$ psql -x -f docittest.sql<br>-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ----------------------------------<br>QUERY PLAN | Nested Loop Left Join&nbsp; (cost=1.28..355.98 rows=104 width=32) (actual time=4.017..4.684 rows=6 loops=1)<br>-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ----------------------------------<br>QUERY PLAN |&nbsp;&nbsp; -&gt;&nbsp; Nested Loop Left Join&nbsp; (cost=0.85..310.53 rows=39 width=26) (actual time=0.134..0.283 rows=6 loops=1)<br>-[ RECORD 3 ]-----------------------------------------------------------------------------------------------------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ----------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using u_dktentry_pkey on u_dktentry x0&nbsp; (cost=0.42..60.94 rows=20 width=16) (actual&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; time=0.093..0.131 rows=5 loops=1)<br>-[ RECORD 4 ]-----------------------------------------------------------------------------------------------------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ----------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (de_caseid = 800)<br>-[ RECORD 5 ]-----------------------------------------------------------------------------------------------------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ----------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: ((de_restrict_view = 0) OR (may_access_docket((de_restrict_view)::integer, de_caseid,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; de_seqno, 0, 0) &lt;&gt; 0))<br>-[ RECORD 6 ]-----------------------------------------------------------------------------------------------------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ----------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Index Scan using u_document_pkey on u_document x0_1&nbsp; (cost=0.43..12.46 rows=2 width=14) (actual&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; time=0.028..0.029 rows=1 loops=5)<br>-[ RECORD 7 ]-----------------------------------------------------------------------------------------------------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ----------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (x0.de_doc_id = dm_id)<br>-[ RECORD 8 ]-----------------------------------------------------------------------------------------------------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ----------------------------------<br>QUERY PLAN |&nbsp;&nbsp; -&gt;&nbsp; Index Scan using u_document_pkey on u_document x0_2&nbsp; (cost=0.43..1.16 rows=1 width=10) (actual time=0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .732..0.732 rows=0 loops=6)<br>-[ RECORD 9 ]-----------------------------------------------------------------------------------------------------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ----------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: ((x0_1.dm_id = dm_id) AND (x0_1.dm_seq = dm_seq))<br>-[ RECORD 10 ]----------------------------------------------------------------------------------------------------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ----------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: ((dm_restrict_view = 0) OR (may_access_doc((dm_restrict_view)::integer, dm_id, dm_seq, dm_t&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ype) &lt;&gt; 0))<br>-[ RECORD 11 ]----------------------------------------------------------------------------------------------------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ----------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Rows Removed by Filter: 1<br>-[ RECORD 12 ]----------------------------------------------------------------------------------------------------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ----------------------------------<br>QUERY PLAN | Planning time: 1.579 ms<br>-[ RECORD 13 ]----------------------------------------------------------------------------------------------------------&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ----------------------------------<br>QUERY PLAN | Execution time: 4.797 ms<br><br></div><div>Which is pretty quick on the 9.5</div><div><br></div><div>******************* 9.6 Version</div><div><br></div><div>On the 9.6 servers we get the following:</div><div><br></div><div>-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN | Merge Left Join&nbsp; (cost=878831.70..8759238.40 rows=473192005 width=38) (actual time=15035.085..16338.751 rows=6 loops=1)<br>-[ RECORD 2 ]----------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp; Merge Cond: (x0.de_doc_id = x0_1.dm_id)<br>-[ RECORD 3 ]----------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp; -&gt;&nbsp; Sort&nbsp; (cost=4770.34..4775.72 rows=2153 width=16) (actual time=0.157..0.159 rows=5 loops=1)<br>-[ RECORD 4 ]----------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: x0.de_doc_id<br>-[ RECORD 5 ]----------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Method: quicksort&nbsp; Memory: 25kB<br>-[ RECORD 6 ]----------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Heap Scan on u_dktentry x0&nbsp; (cost=41.19..4651.15 rows=2153 width=16) (actual time=0.075..0.130 rows=5 loops=1)<br>-[ RECORD 7 ]----------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Recheck Cond: (de_caseid = 800)<br>-[ RECORD 8 ]----------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: ((de_restrict_view = 0) OR (may_access_docket((de_restrict_view)::integer, de_caseid, de_seqno, 0, 0) &lt;&gt; 0))<br>-[ RECORD 9 ]----------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Heap Blocks: exact=4<br>-[ RECORD 10 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Bitmap Index Scan on u_dktentry_pkey&nbsp; (cost=0.00..40.65 rows=2164 width=0) (actual time=0.052..0.052 rows=5 loops=1)<br>-[ RECORD 11 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (de_caseid = 800)<br>-[ RECORD 12 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp; -&gt;&nbsp; Materialize&nbsp; (cost=874061.36..1766468.42 rows=43956527 width=26) (actual time=15024.172..16223.201 rows=1182655 loops=1)<br>&nbsp;RECORD 13 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Merge Left Join&nbsp; (cost=874061.36..1656577.10 rows=43956527 width=26) (actual time=15024.165..15990.605 rows=1182655 loops=1)<br>-[ RECORD 14 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Merge Cond: ((x0_1.dm_id = x0_2.dm_id) AND (x0_1.dm_seq = x0_2.dm_seq))<br>-[ RECORD 15 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Sort&nbsp; (cost=266298.76..269622.02 rows=1329305 width=14) (actual time=1119.467..1344.975 rows=1182655 loops=1)<br>-[ RECORD 16 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: x0_1.dm_id, x0_1.dm_seq<br>-[ RECORD 17 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Method: external merge&nbsp; Disk: 31168kB<br>-[ RECORD 18 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on u_document x0_1&nbsp; (cost=0.00..108375.05 rows=1329305 width=14) (actual time=0.015..550.634 rows=1329305 loops=1)<br>-[ RECORD 19 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Materialize&nbsp; (cost=607762.60..614376.06 rows=1322692 width=16) (actual time=13904.690..14203.239 rows=833199 loops=1)<br>-[ RECORD 20 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Sort&nbsp; (cost=607762.60..611069.33 rows=1322692 width=16) (actual time=13904.688..14097.990 rows=833199 loops=1)<br>-[ RECORD 21 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: x0_2.dm_id, x0_2.dm_seq<br>-[ RECORD 22 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Method: external merge&nbsp; Disk: 19168kB<br>-[ RECORD 23 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp; Seq Scan on u_document x0_2&nbsp; (cost=0.00..450671.09 rows=1322692 width=16) (actual time=3.134..13444.415 rows=931306 loops=1)</div><div>-[ RECORD 24 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: ((dm_restrict_view = 0) OR (may_access_doc((dm_restrict_view)::integer, dm_id, dm_seq, dm_type) &lt;&gt; 0))<br>-[ RECORD 25 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Rows Removed by Filter: 397999<br>-[ RECORD 26 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN | Planning time: 1.567 ms<br>-[ RECORD 27 ]---------------------------------------------------------------------------------------------------------------------------------------------------------<br>QUERY PLAN | Execution time: 16347.890 ms<br><br></div><div><br></div><div>Can anyoen explain WHY there is such a big difference? Is it the SQL statement or a bug in the pgdump ?<br></div><div><br></div><div><br></div><div><br></div><div><span style="font-size: 8pt;" mce_style="font-size: 8pt;"></span><span style="font-size: 8pt;" mce_style="font-size: 8pt;">Ben Duncan - Business Network Solutions, Inc. 336 Elton Road Jackson MS, 39212<br>"Never attribute to malice, that which can be adequately explained by stupidity"<br>- Hanlon's Razor<br></span></div></span></body></html>

Attachment Content-Type Size
unknown_filename text/html 18.8 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Don Seiler 2018-06-13 16:24:35 Re: Issues on 9.5 to 9.6 Upgrade.
Previous Message Ozz Nixon 2018-06-13 16:09:04 Re: