Re: Too many SET TimeZone and Application_name queries

[Adrian Klaver]

On 10/13/19 10:24 PM, Amarendra Konda wrote:

Hi Adrian,

Thanks a lot for the response.

We are using JDBC Driver 42.2.8 along with the Tomcat Server on Java 8. 
As part of application code, We are *_not_* setting timezone (or) 
application names. One observation was, application was querying columns 
of the datatype "timestamp without time zone" .


Well something is explicitly setting the TimeZone. Per this:

https://stackoverflow.com/questions/18447995/postgresql-9-2-jdbc-driver-uses-client-time-zone

I would start with the JDBC driver. You might also try the Postgres JDBC 
list:


https://www.postgresql.org/list/pgsql-jdbc/


Re: application_name. I do not see SET for this when I connect using 
application_name as part of connection string:


psql "host=localhost dbname=postgres user=postgres 
application_name=psql_client"


[unknown]-[unknown]-2019-10-14 07:06:35.508 PDT-0LOG:  connection 
received: host=::1 port=46246
[unknown]-postgres-2019-10-14 07:06:35.530 PDT-0LOG:  connection 
authorized: user=postgres database=postgres SSL enabled 
(protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, bits=256, 
compression=off)


So I believe this is being explicitly SET by something. Since 
'PostgreSQL JDBC Driver' is the Postgres JDBC driver name I would start 
there.




Regards, Amarendra


On Fri, Oct 11, 2019 at 7:33 PM Adrian Klaver > wrote:


On 10/11/19 4:49 AM, Amarendra Konda wrote:
 > Hi,
 >
 > In our test environment, it was observed that there are too many
queries
 > were getting fired to the database server, even though they are
not part
 > of the SQL query execution.
 >
 > And the number of queries that were coming to server are very
high. Can
 > you please suggest on how to avoid these queries to the database
server ?

My guess is your application server/framework is setting the below.
What are you using for above?

 >
 >
 > 2019-10-10 13:37:25 UTC:172.31.77.194(36920):
 > user1@new_unity_green1:[2549]:LOG:  duration: 0.081 ms
  statement: *SET
 > application_name='PostgreSQL JDBC Driver';*
 > 2019-10-10 13:37:25 UTC:172.31.69.112(45682):
 > user1@new_unity_green0:[3545]:LOG:  duration: 0.036 ms
  statement: *SET
 > TimeZone='UTC';*
 > 2019-10-10 13:37:25
 > UTC:172.31.77.194(36902):user1@new_unity_green1:[2112]:LOG:
  duration:
 > 0.177 ms  statement: *SET TimeZone='Etc/UTC';SET
 > application_name='PostgreSQL JDBC Driver';*
 >
 >
 > *_Environment_*
 >
 >   * PGBouncer 1.9
 >   * JDBC Driver 42.2.8
 >   * Java 1.8
 >   * PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc
(GCC) 4.8.3
 >     20140911 (Red Hat 4.8.3-9), 64-bit
 >
 >
 > Application Server, pgBouncer and database server are all configured
 > with UTC only.
 >
 > =>show timezone;
 >   TimeZone
 > --
 >   UTC
 >
 > Thanks in advance,
 >
 > Regards, Amarendra
 >
 >
 >
 >
 >
 >


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Postgres 10.7 Systemd Startup Issue

[Tom Lane]

"Lu, Dan"  writes:
> Is there a catalog view that I can query to see what options were used
> to configure the PG instance?

No, but pg_config should tell you.

regards, tom lane

Re: How to make runtime partition pruning work?

[David Rowley]

On Fri, 11 Oct 2019 at 21:49, Markus Heiden  wrote:
>
> I partitioned a table "data_table" by the key "import_id" to reduce the
> number of partitions to be loaded in my queries.
> I used list partitions, each containing usually just one "import_id". I
> used a primary key (id, import_id)
> But PostgreSQL does not consider partition keys to avoid loading not
> needed partitions.
>
> My query:
> SELECT SUM(something) FROM data_table WHERE import_id IN (SELECT id FROM
> import_table WHERE ...)
> My problem:
> The query takes too long, because PostgreSQL uses a hash join over all
> partitions of "data_table" with the "import_table", instead of pruning
> the "data_table" partitions by the import_ids at runtime.
> Static pruning (when using ... IN (1, 2, 3, 4)) works fine though.
>
> What am I doing wrong that runtime partition pruning with PostgreSQL
> 11.5 does not work in my case?

The documentation for this reads:

"Partition pruning can be performed not only during the planning of a
given query, but also during its execution. This is useful as it can
allow more partitions to be pruned when clauses contain expressions
whose values are not known at query planning time; for example,
parameters defined in a PREPARE statement, using a value obtained from
a subquery or using a parameterized value on the inner side of a
nested loop join. Partition pruning during execution can be performed
at any of the following times:

During initialization of the query plan. Partition pruning can be
performed here for parameter values which are known during the
initialization phase of execution. Partitions which are pruned during
this stage will not show up in the query's EXPLAIN or EXPLAIN ANALYZE.
It is possible to determine the number of partitions which were
removed during this phase by observing the “Subplans Removed” property
in the EXPLAIN output.

During actual execution of the query plan. Partition pruning may also
be performed here to remove partitions using values which are only
known during actual query execution. This includes values from
subqueries and values from execution-time parameters such as those
from parameterized nested loop joins. Since the value of these
parameters may change many times during the execution of the query,
partition pruning is performed whenever one of the execution
parameters being used by partition pruning changes. Determining if
partitions were pruned during this phase requires careful inspection
of the loops property in the EXPLAIN ANALYZE output. Subplans
corresponding to different partitions may have different values for it
depending on how many times each of them was pruned during execution.
Some may be shown as (never executed) if they were pruned every time."

https://www.postgresql.org/docs/11/ddl-partitioning.html#DDL-PARTITION-PRUNING

Notice that only subqueries and parameterized nested loop joins are
mentioned.  The above text does not really go into the detail of which
types of subqueries can be used, but I can confirm that they must be
subqueries that can only return a scalar value. e.g WHERE x = (SELECT
y FROM ...).  The executor would raise an error if that subquery
returned more than 1 row. The IN() clause you have is not eligible.
This will be converted into a semi-join during planning, and even if
it wasn't, the executor wouldn't be raising an error if it returned
multiple rows.

Unfortunately, to do what you mention with a hash join, we'd need to
scan through the entire hash table and incrementally build the set of
partitions which could match each value in the table.  I'm sure there
are times where that would be well worth the trouble, but I imagine in
the average case we might find that the cost of scanning the entire
table this way would be more than just executing the query without any
partition pruning. I don't see any good way to know in advance if it
would be worthwhile or not.

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