Re: Modification of data in base folder and very large tables

[Jerry Sievers]

Ogden Brash  writes:

> I have a question about the files in .../data/postgresql/11/main/
> base, specifically in relation to very large tables and how they are
> written.
>
> I have been attempting to restore a relatively large database with
> pg_restore and it has been running for more than a week. (I also 
> have another thread about the same restore related to network vs.
> local disk I/O)
>
> I ran the pg_restore in verbose mode using multiple jobs so I can
> tell what has finished and what has not. The database had 66 tables
> and most of them have been restored. Two of the tables were quite
> large (billions of rows translating to between 1 and 2TB of data on
> disk for those two tables) and those two tables are pretty much the
> only things remaining that has not been reported as finished by
> pg_restore.
>
> As the process has been going for a week, I have been tracking the
> machine (a dedicated piece of hardware, non-virtualized) and have
> been noticing a progressive slowdown (as tracked by iostat). There is

Do the tables that are being loaded have any indexes on them?

> nothing running on the machine besides postgresql and the server is
> only doing this restore, nothing else. It is now, on average, running
> at less than 25% of the speed that it was running four days ago (as
> measured by rate of I/O). 
>
> I started to dig into what was happening on the machine and I noticed
> the following:
>
> iotop reports that two postgres processes (I assume each processing
> one of the two tables that needs to be processed) are doing all the I
> /O. That makes sense
>
> Total DISK READ :    1473.81 K/s | Total DISK WRITE :     617.30 K/s
> Actual DISK READ:    1473.81 K/s | Actual DISK WRITE:       0.00 B/s
>   TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>   
> COMMAND
>  6601 be/4 postgres  586.44 K/s    7.72 K/s  0.00 % 97.39 % postgres:
> 11/main: postg~s thebruteff [local] COPY
>  6600 be/4 postgres  887.37 K/s  601.87 K/s  0.00 % 93.42 % postgres:
> 11/main: postg~s thebruteff [local] COPY
>   666 be/3 root        0.00 B/s    7.72 K/s  0.00 %  5.73 % [jbd2/
> sda1-8]
>     1 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % init
>     2 be/4 root        0.00 B/s    0.00 B/s  0.00 %  0.00 %
> [kthreadd]
>     4 be/0 root        0.00 B/s    0.00 B/s  0.00 %  0.00 % [kworker/
> 0:0H]
>
> So, the next thing I though I would do is an "lsof" command for each
> of those two processes to see what they were writing. That was a bit
> of a surpise:
>
> # lsof -p 6600 | wc -l;
> 840
>
> # lsof -p 6601 | wc -l;
> 906
>
> Is that normal? That there be so many open file pointers? ~900 open
> file pointers for each of the processes?
>
> The next I did was go to see the actual data files, to see how many
> there are. In my case they are in postgresql/11/main/base/24576 and
> there are 2076 files there. That made sense. However, I found that
> when I list them by modification date I see something interesting:
>
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.7
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.8
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.9
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.10
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.11
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.12
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.13
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.14
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.16
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.15
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.17
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.18
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.19
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.21
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.22
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.23
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.24
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.25
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.26
> -rw--- 1 postgres postgres   19062784 Oct  8 13:05 27082_fsm
> -rw--- 1 postgres postgres  544489472 Oct  8 13:05 27077.34
> -rw--- 1 postgres postgres  169705472 Oct  8 13:05 27082.72
> -rw--- 1 postgres postgres  978321408 Oct  8 13:05 27083.27
> -rw--- 1 postgres postgres  342925312 Oct  8 13:05 27076.88
>
> If you notice, the file size is capped at 1 GB and as the giant table
> has grown it has added more files in this directory. However, the
> mysterious thing to me is that it keeps modifying those files
> constantly - even the ones that are completely full. So for the two
> large tables it has been restoring all week, the modification time
> for the ever growing list of files is being updating constantly.
>
> Could it be that thats why 

Re: Query slow again after adding an `OR` operation (was: Slow PostgreSQL 10.6 query)

[David Rowley]

On Thu, 10 Oct 2019 at 01:07, Behrang Saeedzadeh  wrote:
>
> This is a follow up to 
> https://www.postgresql.org/message-id/flat/CAERAJ%2B-1buiJ%2B_JWEo0a9Ao-CVMWpgp%3DEnFx1dJtnB3WmMi2zQ%40mail.gmail.com
>
> The query (generated by Hibernate) got a bit more complex and performance 
> degraded again. I have uploaded all the details here (with changed table 
> names, etc.): https://github.com/behrangsa/slow-query
>
> In short, the new query is:

The query mostly appears slow due to the "Rows Removed By Filter" in
the OR condition. The only way to get around not scanning the entire
branch_invoices table would be to somehow write the way in such a way
that allows it to go on the inner side of the join.

You could do that if you ensure there's an index on branch_invoices
(branch_id) and format the query as:

SELECT inv.id   AS i_id,
   inv.invoice_date AS inv_d,
   inv.invoice_xid  AS inv_xid,
   inv.invoice_type AS inv_type,
   brs.branch_idAS br_id,
   cinvs.company_id AS c_id
FROM invoices inv
 LEFT OUTER JOIN branch_invoices brs ON inv.id = brs.invoice_id
 LEFT OUTER JOIN company_invoices cinvs ON inv.id = cinvs.invoice_id
 INNER JOIN branches br ON brs.branch_id = br.id
WHERE brs.branch_id IN (SELECT br1.id
FROM branches br1
 INNER JOIN access_rights ar1 ON
br1.id = ar1.branch_id
 INNER JOIN users usr1 ON ar1.user_id = usr1.id
 INNER JOIN groups grp1 ON
ar1.group_id = grp1.id
 INNER JOIN group_permissions gpr1 ON
grp1.id = gpr1.group_id
 INNER JOIN permissions prm1 ON
gpr1.permission_id = prm1.id
WHERE usr1.id = 1636
  AND prm1.code = 'C2'
  AND ar1.access_type = 'T1')
UNION ALL
SELECT br3.id
FROM companies cmp
 INNER JOIN branches br3 ON cmp.id =
br3.company_id
 INNER JOIN access_rights ar2 ON
cmp.id = ar2.company_id
 INNER JOIN users usr2 ON ar2.user_id = usr2.id
 INNER JOIN groups g2 ON ar2.group_id = g2.id
 INNER JOIN group_permissions gpr2 ON
g2.id = gpr2.group_id
 INNER JOIN permissions prm2 ON
gpr2.permission_id = prm2.id
WHERE usr2.id = 1636
  AND prm2.code = 'C2'
  AND ar2.access_type = 'T1')
ORDER BY inv.invoice_date DESC, br.name ASC
LIMIT 12;

The planner may then choose to pullup the subquery and uniquify it
then put it on the outside of a nested loop join then lookup the
branch_invoices record using the index on branch_id.  I think this is
quite a likely plan since the planner estimates there's only going to
be 1 row from each of the subqueries.

Also note, that the LEFT JOIN you have to branch_invoices is not
really a left join since you're insisting that the branch_id must be
in the first or 2nd sub-plan. There's no room for it to be NULL. The
planner will just convert that to an INNER JOIN with the above query
since that'll give it the flexibility to put the subquery in the IN
clause on the outside of the join (after having uniquified it).
You'll need to decide what you actually want the behaviour to be here.
If you do need those NULL rows then you'd better move your WHERE quals
down into the join condition for branch_invoices table. I'd suggest
testing with some mock-up data if you're uncertain of what I mean.

If you find that is faster and you can't rewrite the query due to it
having been generated by Hibernate, then that sounds like a problem
with Hibernate.  PostgreSQL does not currently attempt to do any
rewrites which convert OR clauses to use UNION or UNION ALL. No amount
of tweaking the planner settings is going to change that fact.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Would SSD improve Index Only Scan performance by a lot?

[Jeff Janes]

On Tue, Oct 8, 2019 at 7:37 PM Arya F  wrote:

> As my table has gotten bigger, it takes longer to get a single row back
> when querying a row by its btree index.
>
>
Is this in a probabilistic sense, they take longer on average, or has every
single access gotten slower?  If the increase in size has caused the index
leaf pages to go from being almost entirely in cache to almost entirely not
being in cache, the slow down would probably be a lot more 3 to 4 fold.
But maybe you went from 100% in cache, to 90% in cache and 10% out of cache
(with a 40 fold slowdown for those ones), coming out to 4 fold slow down on
average.  If that is the case, maybe you can get the performance back up by
tweaking some settings, rather than changing hardware.


> Right now the database is running on a traditional HDD. SSDs have a much
> faster seek time than traditional HDDs.
>
> Would switching to an SSD improve "Index Only Scan" time greatly? by at
> least 3-4 times?
>

If drive access is truly the bottleneck on every single execution, then
yes, probably far more than 3-4 times.

Cheers,

Jeff

Re: Query slow again after adding an `OR` operation (was: Slow PostgreSQL 10.6 query)

[Michael Lewis]

Are you prefixing this auto generated query with set join_collapse_limit =
30, or are you changing the default and reloading config? That is, can you
impact ONLY this query with these config changes? I wouldn't assume so, so
any hack/query hint like turning off hashjoins (which seem to be chosen
instead of nested loop because of bad estimates for this plan) will likely
have serious impact on other queries.

I know you don't have the flexibility to change the query to be one that
follows best practices, but it is a bit disappointing that your ORM
generates that OR condition instead of something like *brs.branch_id IN
(query1 union all query2). *The join to branch_invoices also must function
as inner join rather than left, but I am not sure if declaring a join type
as left impacts the performance significantly.

When performance matters, there's nothing quite like being able to
customize the query directly.

Re: Modification of data in base folder and very large tables

[Jeff Janes]

On Wed, Oct 9, 2019 at 4:33 AM Ogden Brash  wrote:

> # lsof -p 6600 | wc -l;
> 840
>
> # lsof -p 6601 | wc -l;
> 906
>
> Is that normal? That there be so many open file pointers? ~900 open file
> pointers for each of the processes?
>

I don't think PostgreSQL makes any effort to conserve file handles, until
it starts reaching the max.  So any file that has ever been opened will
remain open, unless it was somehow invalidated (e.g. the file needs to be
deleted).  If those processes were previously loading smaller tables before
the got bogged down in the huge ones, a large number of handles would not
be unexpected.



> The next I did was go to see the actual data files, to see how many there
> are. In my case they are in postgresql/11/main/base/24576 and there are
> 2076 files there. That made sense. However, I found that when I list them
> by modification date I see something interesting:
>
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.7
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.8
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.9
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.10
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.11
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.12
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.13
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.14
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.16
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.15
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.17
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.18
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.19
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.21
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.22
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.23
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.24
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.25
> -rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.26
> -rw--- 1 postgres postgres   19062784 Oct  8 13:05 27082_fsm
> -rw--- 1 postgres postgres  544489472 Oct  8 13:05 27077.34
> -rw--- 1 postgres postgres  169705472 Oct  8 13:05 27082.72
> -rw--- 1 postgres postgres  978321408 Oct  8 13:05 27083.27
> -rw--- 1 postgres postgres  342925312 Oct  8 13:05 27076.88
>
> If you notice, the file size is capped at 1 GB and as the giant table has
> grown it has added more files in this directory. However, the mysterious
> thing to me is that it keeps modifying those files constantly - even the
> ones that are completely full. So for the two large tables it has been
> restoring all week, the modification time for the ever growing list of
> files is being updating constantly.
>

The bgwriter, the checkpointer, and autovac, plus any backends that decide
they need a clean page from the buffer cache can all touch those files.
They might touch them in ways that are not IO intensive, but still cause
the modification time to get updated. In my hands, one all dirty buffers a
given file have been flushed and all contents in the file have been
vacuumed, its mtime stops changing just due to copy in which is directed to
later files.

It is also squishy what it even means to modify a file.  I think
filesystems have heuristics to avoid "gratuitous" updates to mtime, which
make it hard to recon with.


>
> Could it be that thats why I am seeing a slowdown over the course of the
> week - that for some reason as the number of files for the table has grown,
> the system is spending more and more time seeking around the disk to touch
> all those files for some reason?
>

I don't think lsof or mtime are effective ways to research this.  How about
running strace -ttt -T -y on those processes?

Cheers,

Jeff

Re: Get the planner used by a query?

[David Rowley]

On Wed, 9 Oct 2019 at 19:21, Behrang Saeedzadeh  wrote:
>
> Is there a way to display the planner algorithm used by a query, either in 
> EXPLAIN or in a different way?

There's not really any simple way to know. If the number of relations
in the join search meets or exceeds geqo_threshold then it'll use the
genetic query optimizer. However, knowing exactly how many relations
are in the join search is not often simple since certain types of
subqueries can be pulled up into the main query and that can increase
the number of relations in the search.

If you don't mind writing C code, then you could write an extension
that hooks into join_search_hook and somehow outputs this information
to you before going on to call the geqo if the "enable_geqo &&
levels_needed >= geqo_threshold" condition is met.  Besides that, I
don't really know if there's any way.  You could try editing the
geqo_seed and seeing if the plan changes, but if it does not, then
that does not mean the geqo was not used, so doing it that way could
be quite error-prone.  You'd only be able to tell the geqo was being
used if you could confirm that changing geqo_seed did change the plan.
(And you could be certain the plan did not change for some other
reason like an auto-analyze).



--
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Query slows when used with view

[Tom Lane]

Michael Lewis  writes:
>> When you join to a view, the view sticks together, as if they were all in
>> parentheses.   But when you substitute the text of a view into another
>> query, then they are all on the same level and can be parsed differently.
>> 
>> Consider the difference between "1+1 * 3", and "(1+1) * 3"

> I thought from_collapse_limit being high enough meant that it will get
> re-written and inlined into the same level. To extend your metaphor, that
> it would be 1 * 3 + 1 * 3.

The point is that the semantics are actually different --- in Jeff's
example, the answer is 4 vs. 6, and in the OP's query, the joins have
different scopes.  from_collapse_limit has to do with whether the
planner can rewrite the query into a different form, but it's not
allowed to change the semantics by doing so.

In some cases you can re-order joins without changing the semantics,
just as arithmetic has associative and commutative laws.  But you
can't always re-order outer joins like that.  I didn't dig into
the details of the OP's query too much, but I believe that the two
forms of his join tree are semantically different, resulting
in different runtimes.

regards, tom lane

Re: Query slows when used with view

[Michael Lewis]

>
> When you join to a view, the view sticks together, as if they were all in
> parentheses.   But when you substitute the text of a view into another
> query, then they are all on the same level and can be parsed differently.
>
> Consider the difference between "1+1 * 3", and "(1+1) * 3"
>

I thought from_collapse_limit being high enough meant that it will get
re-written and inlined into the same level. To extend your metaphor, that
it would be 1 * 3 + 1 * 3.

Re: Query slows when used with view

[Jeff Janes]

On Wed, Oct 9, 2019 at 10:56 AM Yavuz Selim Sertoğlu (ETIYA) <
yavuz.serto...@etiya.com> wrote:

> Thanks for the reply Tom,
>
> Sorry, I couldn't understand. I just copied inside of view and add
> conditions from query that runs with view.
> The comma parts are the same in two queries, one is inside of view the
> other is in the query.
>

When you join to a view, the view sticks together, as if they were all in
parentheses.   But when you substitute the text of a view into another
query, then they are all on the same level and can be parsed differently.

Consider the difference between "1+1 * 3", and "(1+1) * 3"

Cheers,

Jeff

Re: Query slows when used with view

[Michael Lewis]

>
> Those are not equivalent queries.  Read up on the syntax of FROM;
> particularly, that JOIN binds more tightly than comma.
>

I see this-

"A JOIN clause combines two FROM items, which for convenience we will refer
to as “tables”, though in reality they can be any type of FROM item. Use
parentheses if necessary to determine the order of nesting. In the absence
of parentheses, JOINs nest left-to-right. In any case JOIN binds more
tightly than the commas separating FROM-list items."
https://www.postgresql.org/docs/current/sql-select.html

What is meant by nesting? Or binding for that matter? I wouldn't expect
increasing from/join_collapse_limit to be helpful to the original poster
since they haven't exceeded default limit of 8. Any further clarification
elsewhere you could point to?

RE: Query slows when used with view

[ETIYA]

Thanks for the reply Tom,

Sorry, I couldn't understand. I just copied inside of view and add conditions 
from query that runs with view.
The comma parts are the same in two queries, one is inside of view the other is 
in the query.


-Original Message-
From: Tom Lane 
Sent: 09 October 2019 16:57
To: Yavuz Selim Sertoğlu (ETIYA) 
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Query slows when used with view

=?iso-8859-9?Q?Yavuz_Selim_Serto=F0lu_=28ETIYA=29?=  
writes:
> I have a problem with views. When I use view in my query it really slows 
> down(1.7seconds)
> If I use inside of view and add conditions and joins to it, it is really 
> fast(0.7 milliseconds).
> I have no distinct/group/partition by in view so I have no idea why is this 
> happening.
> I wrote queries and plans below.

Those are not equivalent queries.  Read up on the syntax of FROM;
particularly, that JOIN binds more tightly than comma.

regards, tom lane
[http://www.etiya.com/images/e-newsletter/signature/e_logo_1.png]
[http://www.etiya.com/images/e-newsletter/signature/e_adres.png]
[http://www.etiya.com/images/e-newsletter/signature/facebook_icon.png]
 [http://www.etiya.com/images/e-newsletter/signature/linkedin_icon.png] 

  [http://www.etiya.com/images/e-newsletter/signature/instagram_icon.png] 
  
[http://www.etiya.com/images/e-newsletter/signature/youtube_icon.png] 
  
[http://www.etiya.com/images/e-newsletter/signature/twitter_icon.png] 

[http://www.etiya.com/images/e-newsletter/signature/0.png]

Yavuz Selim Sertoğlu
Solution Support Specialist II

T:+90 312 265 01 50
M:+90 552 997 52 02
E:yavuz.serto...@etiya.com

Üniversiteler Mahallesi 1606.cadde No:4 Cyberpark C Blok Zemin kat ofis no 
:Z25A-Z44
[http://www.etiya.com/images/e-newsletter/signature/tmf_award.jpg] 



Yasal Uyari :
Bu elektronik posta asagidaki adreste bulunan Kosul ve Sartlara tabidir;
http://www.etiya.com/gizlilik

ÇIKTI ALMADAN ÖNCE ÇEVREYE OLAN SORUMLULUGUMUZU BIR KEZ DAHA DÜSÜNELIM.
PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING ANY DOCUMENT.

Re: Query slows when used with view

[Tom Lane]

=?iso-8859-9?Q?Yavuz_Selim_Serto=F0lu_=28ETIYA=29?=  
writes:
> I have a problem with views. When I use view in my query it really slows 
> down(1.7seconds)
> If I use inside of view and add conditions and joins to it, it is really 
> fast(0.7 milliseconds).
> I have no distinct/group/partition by in view so I have no idea why is this 
> happening.
> I wrote queries and plans below.

Those are not equivalent queries.  Read up on the syntax of FROM;
particularly, that JOIN binds more tightly than comma.

regards, tom lane

Query slows when used with view

[ETIYA]

Hi all,

I have a problem with views. When I use view in my query it really slows 
down(1.7seconds)
If I use inside of view and add conditions and joins to it, it is really 
fast(0.7 milliseconds).
I have no distinct/group/partition by in view so I have no idea why is this 
happening.
I wrote queries and plans below.
I would be very happy if you can help me.

Best regards,




Query without view;

explain analyze select
   *
from
   bss.prod_char_val
left join bss.prod on
   prod.prod_id = prod_char_val.prod_id,
   bss.gnl_st prodstatus,
   bss.gnl_char
left join bss.gnl_char_lang on
   gnl_char_lang.char_id = gnl_char.char_id,
   bss.gnl_char_val
left join bss.gnl_char_val_lang on
   gnl_char_val_lang.char_val_id = gnl_char_val.char_val_id,
   bss.gnl_st charvalstatus
   cross join bss.prod  prodentity0_
cross join bss.cust custentity2_
where
   prod.st_id = prodstatus.gnl_st_id
   and (prodstatus.shrt_code::text = any (array['ACTV'::character 
varying::text,
   'PNDG'::character varying::text]))
   and gnl_char_val_lang.is_actv = 1::numeric
   and gnl_char_lang.is_actv = 1::numeric
   and gnl_char_lang.lang::text = gnl_char_val_lang.lang::text
   and prod_char_val.char_id = gnl_char.char_id
   and prod_char_val.char_val_id = gnl_char_val.char_val_id
   and prod_char_val.st_id = charvalstatus.gnl_st_id
   and (charvalstatus.shrt_code::text = any (array['ACTV'::character 
varying::text,'PNDG'::character varying::text]))
   and gnl_char_val_lang.lang = 'en'
   and (charvalstatus.shrt_code = 'xxx'
   and prod_char_val.val = 'xxx'
   or charvalstatus.shrt_code = 'xxx'
   and prod_char_val.val = 'xxx')
   and prodentity0_.prod_id = prod_char_val.prod_id
and custentity2_.party_id = 16424
and prodentity0_.cust_id = custentity2_.cust_id
   order by
   prodentity0_.prod_id desc;


Sort  (cost=373.92..373.93 rows=1 width=19509) (actual time=0.098..0.098 rows=0 
loops=1)
  Sort Key: prod_char_val.prod_id DESC
  Sort Method: quicksort  Memory: 25kB
  ->  Nested Loop  (cost=2.57..373.91 rows=1 width=19509) (actual 
time=0.066..0.066 rows=0 loops=1)
Join Filter: (gnl_char_val.char_val_id = gnl_char_val_lang.char_val_id)
->  Nested Loop  (cost=2.30..373.58 rows=1 width=19447) (actual 
time=0.066..0.066 rows=0 loops=1)
  ->  Nested Loop  (cost=2.15..373.42 rows=1 width=18571) (actual 
time=0.066..0.066 rows=0 loops=1)
Join Filter: (gnl_char.char_id = gnl_char_lang.char_id)
->  Nested Loop  (cost=1.88..373.09 rows=1 width=18488) 
(actual time=0.066..0.066 rows=0 loops=1)
  ->  Nested Loop  (cost=1.73..372.92 rows=1 
width=16002) (actual time=0.066..0.066 rows=0 loops=1)
Join Filter: (charvalstatus.gnl_st_id = 
prod_char_val.st_id)
->  Nested Loop  (cost=1.29..214.51 rows=11 
width=15914) (actual time=0.065..0.065 rows=0 loops=1)
  ->  Nested Loop  (cost=1.15..207.14 
rows=44 width=15783) (actual time=0.065..0.065 rows=0 loops=1)
->  Nested Loop  (cost=0.72..180.73 
rows=44 width=9586) (actual time=0.065..0.065 rows=0 loops=1)
  ->  Seq Scan on gnl_st 
charvalstatus  (cost=0.00..10.61 rows=1 width=131) (actual time=0.064..0.065 
rows=0 loops=1)
Filter: 
(((shrt_code)::text = ANY ('{ACTV,PNDG}'::text[])) AND ((shrt_code)::text = 
'xxx'::text))
Rows Removed by Filter: 
307
  ->  Nested Loop  
(cost=0.72..169.68 rows=44 width=9455) (never executed)
->  Index Scan using 
idx_cust_party_id on cust custentity2_  (cost=0.29..8.31 rows=1 width=3258) 
(never executed)
  Index Cond: 
(party_id = '16424'::numeric)
->  Index Scan using 
idx_prod_cust_id on prod prodentity0_  (cost=0.43..160.81 rows=57 width=6197) 
(never executed)
  Index Cond: 
(cust_id = custentity2_.cust_id)
->  Index Scan using pk_prod on 
prod  (cost=0.43..0.60 rows=1 width=6197) (never executed)
  Index Cond: (prod_id = 
prodentity0_.prod_id)
  ->  Index Scan using gnl_st_pkey on 
gnl_st prodstatus  (cost=0.15..0.17 rows=1 width=131) (never executed)
Index Cond: (gnl_st_id = prod.st_id)
Filter: ((shrt_code)::text = ANY 
('{ACTV,PNDG}'::text[]))
 

Query slow again after adding an `OR` operation (was: Slow PostgreSQL 10.6 query)

[Behrang Saeedzadeh]

This is a follow up to
https://www.postgresql.org/message-id/flat/CAERAJ%2B-1buiJ%2B_JWEo0a9Ao-CVMWpgp%3DEnFx1dJtnB3WmMi2zQ%40mail.gmail.com

The query (generated by Hibernate) got a bit more complex and performance
degraded again. I have uploaded all the details here (with changed table
names, etc.): https://github.com/behrangsa/slow-query

In short, the new query is:

```

SELECT inv.id   AS i_id,
   inv.invoice_date AS inv_d,
   inv.invoice_xid  AS inv_xid,
   inv.invoice_type AS inv_type,
   brs.branch_idAS br_id,
   cinvs.company_id AS c_idFROM invoices inv
 LEFT OUTER JOIN branch_invoices brs ON inv.id = brs.invoice_id
 LEFT OUTER JOIN company_invoices cinvs ON inv.id = cinvs.invoice_id
 INNER JOIN branches br ON brs.branch_id = br.idWHERE
brs.branch_id IN (SELECT br1.id
FROM branches br1
 INNER JOIN access_rights ar1 ON
br1.id = ar1.branch_id
 INNER JOIN users usr1 ON ar1.user_id = usr1.id
 INNER JOIN groups grp1 ON
ar1.group_id = grp1.id
 INNER JOIN group_permissions gpr1 ON
grp1.id = gpr1.group_id
 INNER JOIN permissions prm1 ON
gpr1.permission_id = prm1.id
WHERE usr1.id = 1636
  AND prm1.code = 'C2'
  AND ar1.access_type = 'T1')
   OR brs.branch_id IN (SELECT br3.id
FROM companies cmp
 INNER JOIN branches br3 ON cmp.id =
br3.company_id
 INNER JOIN access_rights ar2 ON
cmp.id = ar2.company_id
 INNER JOIN users usr2 ON ar2.user_id = usr2.id
 INNER JOIN groups g2 ON ar2.group_id = g2.id
 INNER JOIN group_permissions gpr2 ON
g2.id = gpr2.group_id
 INNER JOIN permissions prm2 ON
gpr2.permission_id = prm2.id
WHERE usr2.id = 1636
  AND prm2.code = 'C2'
  AND ar2.access_type = 'T1'
ORDER BY br3.id)ORDER BY inv.invoice_date
DESC, br.name ASCLIMIT 12;

```

I tried tweaking join_collapse_limit and from_collapse_limit (I tried up to
30) but couldn't improve the performance (I also increased geqo_threshold to
join_collapse_limit + 2).

Any chance of making PostgreSQL 10.6 choose a better plan without rewriting
the Hibernate generated query?

Best regards,
Behrang Saeedzadeh
blog.behrang.org

Re: Modification of data in base folder and very large tables

[Andrew Gierth]

> "Ogden" == Ogden Brash  writes:

 Ogden> I have a question about the files in
 Ogden> .../data/postgresql/11/main/base, specifically in relation to
 Ogden> very large tables and how they are written.

 Ogden> I have been attempting to restore a relatively large database
 Ogden> with pg_restore and it has been running for more than a week.

Did you do the restore into a completely fresh database? Or did you make
the mistake of creating tables and indexes first?

What relation does the filenode 27083 correspond to? You can find that
with:

select oid::regclass from pg_class
 where pg_relation_filenode(oid) = '27083';

-- 
Andrew (irc:RhodiumToad)

Modification of data in base folder and very large tables

[Ogden Brash]

I have a question about the files in .../data/postgresql/11/main/base,
specifically in relation to very large tables and how they are written.

I have been attempting to restore a relatively large database with
pg_restore and it has been running for more than a week. (I also  have
another thread about the same restore related to network vs. local disk I/O)

I ran the pg_restore in verbose mode using multiple jobs so I can tell what
has finished and what has not. The database had 66 tables and most of them
have been restored. Two of the tables were quite large (billions of rows
translating to between 1 and 2TB of data on disk for those two tables) and
those two tables are pretty much the only things remaining that has not
been reported as finished by pg_restore.

As the process has been going for a week, I have been tracking the machine
(a dedicated piece of hardware, non-virtualized) and have been noticing a
progressive slowdown (as tracked by iostat). There is nothing running on
the machine besides postgresql and the server is only doing this restore,
nothing else. It is now, on average, running at less than 25% of the speed
that it was running four days ago (as measured by rate of I/O).

I started to dig into what was happening on the machine and I noticed the
following:

iotop reports that two postgres processes (I assume each processing one of
the two tables that needs to be processed) are doing all the I/O. That
makes sense

Total DISK READ :1473.81 K/s | Total DISK WRITE : 617.30 K/s
Actual DISK READ:1473.81 K/s | Actual DISK WRITE:   0.00 B/s
  TID  PRIO  USER DISK READ  DISK WRITE  SWAPIN IO>COMMAND
 6601 be/4 postgres  586.44 K/s7.72 K/s  0.00 % 97.39 % postgres:
11/main: postg~s thebruteff [local] COPY
 6600 be/4 postgres  887.37 K/s  601.87 K/s  0.00 % 93.42 % postgres:
11/main: postg~s thebruteff [local] COPY
  666 be/3 root0.00 B/s7.72 K/s  0.00 %  5.73 % [jbd2/sda1-8]
1 be/4 root0.00 B/s0.00 B/s  0.00 %  0.00 % init
2 be/4 root0.00 B/s0.00 B/s  0.00 %  0.00 % [kthreadd]
4 be/0 root0.00 B/s0.00 B/s  0.00 %  0.00 % [kworker/0:0H]

So, the next thing I though I would do is an "lsof" command for each of
those two processes to see what they were writing. That was a bit of a
surpise:

# lsof -p 6600 | wc -l;
840

# lsof -p 6601 | wc -l;
906

Is that normal? That there be so many open file pointers? ~900 open file
pointers for each of the processes?

The next I did was go to see the actual data files, to see how many there
are. In my case they are in postgresql/11/main/base/24576 and there are
2076 files there. That made sense. However, I found that when I list them
by modification date I see something interesting:

-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.7
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.8
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.9
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.10
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.11
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.12
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.13
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.14
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.16
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.15
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.17
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.18
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.19
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.21
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.22
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.23
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.24
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.25
-rw--- 1 postgres postgres 1073741824 Oct  8 13:05 27083.26
-rw--- 1 postgres postgres   19062784 Oct  8 13:05 27082_fsm
-rw--- 1 postgres postgres  544489472 Oct  8 13:05 27077.34
-rw--- 1 postgres postgres  169705472 Oct  8 13:05 27082.72
-rw--- 1 postgres postgres  978321408 Oct  8 13:05 27083.27
-rw--- 1 postgres postgres  342925312 Oct  8 13:05 27076.88

If you notice, the file size is capped at 1 GB and as the giant table has
grown it has added more files in this directory. However, the mysterious
thing to me is that it keeps modifying those files constantly - even the
ones that are completely full. So for the two large tables it has been
restoring all week, the modification time for the ever growing list of
files is being updating constantly.

Could it be that thats why I am seeing a slowdown over the course of the
week - that for some reason as the number of files for the table has grown,
the system is spending more and more time seeking around the disk to touch
all those files for some reason?

Does anyone who understands the details of 

Get the planner used by a query?

[Behrang Saeedzadeh]

Is there a way to display the planner algorithm used by a query, either in
EXPLAIN or in a different way?

Regards,
Behrang (sent from my mobile)