Re: 答复: response time is very long in PG9.5.5 using psql or jdbc

Hi,

On 2018-02-13 13:58:00 -0500, Tom Lane wrote:
> =?gb2312?B?yq/Twrui?= <SHIYONGHU651(at)pingan(dot)com(dot)cn> writes:
> > Yes,we have more than 500 thousand of objects,and the total size of the database is almost 10TB.Just as you said,we may need to reduce the objects number,or you have any better solution?

Might also be worth knowing where the time is spent. Any chance you
could use a profiler like perf to figure out exactly where the time is
going?

A simple thing wich might already give some hints is to show us parser &
planner stats:
SET log_parser_stats = true;
SET log_planner_stats = true;
SET client_min_messages = log;
\i t.sql

that should print things like
LOG: 00000: PARSER STATISTICS
DETAIL: ! system usage stats:
! 0.000043 s user, 0.000001 s system, 0.000041 s elapsed
! [3.086537 s user, 0.061010 s system total]
! 100592 kB max resident size
! 0/0 [183368/0] filesystem blocks in/out
! 0/0 [651/3696] page faults/reclaims, 0 [0] swaps
! 0 [0] signals rcvd, 0/0 [0/0] messages rcvd/sent
! 0/0 [818/10] voluntary/involuntary context switches
LOCATION: ShowUsage, postgres.c:4545

Just out of paranoia, could you share the result of:
SHOW ignore_system_indexes;

> Hmph. I tried creating 500000 tables in a test database, and couldn't
> detect any obvious performance problem in session startup.

Did you try 9.5 or master? We've had a few improvements... But I think
the majority are only going to matter when a lot of relations are
touched in the same relation...

Greetings,

Andres Freund