Re: [PERFORM] Store/Retrieve time series data from PostgreSQL

[vinny]

On 2017-09-14 13:51, Subramaniam C wrote:

Hi

QUERY :-

_select distinct on (health_timeseries.mobid) mobid,
health_timeseries.health, health_timeseries.hour from
health_timeseries where hour >=(1505211054000/(3600*1000))-1 and hour
<= 1505211054000/(3600*1000) ORDER BY health_timeseries.mobid DESC,
health_timeseries.hour DESC;_



Did you run EXPLAIN on this query to see what it is actually doing?

What you are doing how is selecting all rows from the last hour,
sorting them by mobid and hour, and then DISTINCT filters out al 
duplicates.


Sorting on mobid is therefor useless, DISTINCT still has to check all 
rows.


Sorting on mobid and hour will take a long time if there is no index for 
it,
so if you don't have an index on the mobid and hour together then you 
should probably try that.



But, see what EXPLAIN tells you first.

Regards,
Vincent.


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] query runs for more than 24 hours!

[vinny]

On 2017-08-22 16:23, Mariel Cherkassky wrote:



SELECT a.inst_prod_id,
   product_id,
  nap_area2,
   nap_phone_num,
 nap_product_id,
   b.nap_discount_num,
  b.nap_makat_cd,
   nap_act_start_dt,
b.nap_debt_line,
   nap_act_end_dt,
   b.row_added_dttm
   b.row_lastmant_dttm,
FROM   ps_rf_inst_prod a,
ANDa.setid  || ''= 'SHARE'
  nap_ip_discount b
WHERE  nap_crm_status  = 'C_04'
ANDb.nap_makat_cd IN (SELECT
term_codeANDb.setid  || ''=
'SHARE'
ANDa.inst_prod_id =



On my screen the order of the lines in the query seem to get messed up,
I'm not sure if that's my email program or a copy/paste error.

From what I can see, you are using subselects in an IN statement,
which can be a problem if that has to be re-evaluated a lot.

It's hard for me to say more because I can't tell what the actual query 
is at the moment.


Regards, Vincent.


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How to grant only create schemas and create database objects permission to user.

[vinny]

On 2017-07-19 14:23, Dinesh Chandra 12108 wrote:

Dear expert,

I have to create a user which have permission to CREATE SCHEMAS AND
CREATE DATABASE OBJECTS in database.

I am using postgres 9.1.

Could you please assist me?


Access control is managed using the GRANT command: 
https://www.postgresql.org/docs/9.1/static/sql-grant.html


About halfway down that page it says:

-
CREATE
For databases, allows new schemas to be created within the database.

For schemas, allows new objects to be created within the schema. To 
rename an existing object, you must own the object and have this 
privilege for the containing schema.


For tablespaces, allows tables, indexes, and temporary files to be 
created within the tablespace, and allows databases to be created that 
have the tablespace as their default tablespace. (Note that revoking 
this privilege will not alter the placement of existing objects.)



I suggest you try it out on a test database first.


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Query with no result set, really really slow adding ORBDER BY / LIMIT clause

[vinny]

On 2017-04-20 13:16, Marco Renzi wrote:

Thanks Philip, yes i tried, but that is not solving, still slow. Take
a look at the log.

--
Limit  (cost=3.46..106.87 rows=10 width=4) (actual
time=396555.327..396555.327 rows=0 loops=1)
  ->  Nested Loop  (cost=3.46..214781.07 rows=20770 width=4) (actual
time=396555.326..396555.326 rows=0 loops=1)
Join Filter: (tipofase.id [1] = fase.tipofase)
->  Index Scan Backward using test_prova_2 on fase
(cost=0.43..192654.24 rows=1474700 width=8) (actual
time=1.147..395710.190 rows=1475146 loops=1)
->  Materialize  (cost=3.03..6.34 rows=1 width=8) (actual
time=0.000..0.000 rows=0 loops=1475146)
  ->  Hash Semi Join  (cost=3.03..6.33 rows=1 width=8)
(actual time=0.081..0.081 rows=0 loops=1)
Hash Cond: (tipofase.id [1] = tipofase_1.id [2])
->  Seq Scan on tipofase  (cost=0.00..3.02
rows=102 width=4) (actual time=0.003..0.003 rows=1 loops=1)
->  Hash  (cost=3.02..3.02 rows=1 width=4) (actual
time=0.064..0.064 rows=0 loops=1)
  Buckets: 1024  Batches: 1  Memory Usage: 0kB
  ->  Seq Scan on tipofase tipofase_1
(cost=0.00..3.02 rows=1 width=4) (actual time=0.063..0.063 rows=0
loops=1)
Filter: agendafrontoffice
Rows Removed by Filter: 102
Planning time: 1.254 ms
Execution time: 396555.499 ms

--

THE ONLY WAY TO SPEEDUP I FOUND IS THIS ONE

SELECT  fase.id [3]
FROMtipofase
JOIN   fase
ON (fase.tipofase = (SELECT tipofase.id [1] FROM tipofase
WHERE tipofase.agendafrontoffice = true))

ORDER BYfase.id [3] DESC   limit 10 offset 0

--

Limit  (cost=3.45..3.58 rows=10 width=4) (actual time=0.082..0.082
rows=0 loops=1)
  InitPlan 1 (returns $0)
->  Seq Scan on tipofase tipofase_1  (cost=0.00..3.02 rows=1
width=4) (actual time=0.072..0.072 rows=0 loops=1)
  Filter: agendafrontoffice
  Rows Removed by Filter: 102
  ->  Nested Loop  (cost=0.43..27080.93 rows=2118540 width=4) (actual
time=0.081..0.081 rows=0 loops=1)
->  Index Only Scan Backward using fase_test_prova_4 on fase
(cost=0.43..595.90 rows=20770 width=4) (actual time=0.080..0.080
rows=0 loops=1)
  Index Cond: (tipofase = $0)
  Heap Fetches: 0
->  Materialize  (cost=0.00..3.53 rows=102 width=0) (never
executed)
  ->  Seq Scan on tipofase  (cost=0.00..3.02 rows=102
width=0) (never executed)
Planning time: 0.471 ms
Execution time: 0.150 ms

--

Anyone knows?
I'm a bit worried about performance in my web app beacause sometimes
filters are written dinamically at the end, and i would like to avoid
these problems.




What was it that Philip suggested? I can't find his reply in the list 
and you didn't quote it...


Did you try reversing the order of the tables, so join fase to tipofase, 
instead of tipofase to fase.
Also, did you try a partial index on tipofase.id where 
tipofase.agendafrontoffice = true?



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Understanding PostgreSQL query execution time

[vinny]

On 2017-04-07 16:56, Haider Ali wrote:

Hello

I want to understand execution time of a query in PostgreSQL then I
want to relate it to the problem i am getting. According to my
observation ( I can't explain why this happen ) whenever we query a
table first time its execution will be high (sometimes very high) as
compare to queries made on same table in a short period of time
followed by first query on that table. For example query given below


The first time a query is executed it is quite likely that the data it 
needs

is not in RAM yet, so it must fetch the data from disk, which is slow.

But, benchmarking is an art; did you execute these queries separately 
from the commandline?

Otherwise where may be other forces at work here...



Having experience above behaviour of PostgreSQL now I am using
PostgreSQL managed by Amazon RDS. Observation is no matter how many
times I execute same query its execution times remain same ( although
execution time of a query on RDS is comparatively high as compare to
query running on local instance of PostgreSQL that I can understand is
because of Network latency)


The problem may go away entirely if the database/OS has enough RAM 
available,

and configured, for caching.

The problem on your local system may be simply a case of PostgreSQL or 
the OS
removing tuples/index data from RAM when it feels it can make better use 
of that RAM

space for other things if you don't access that data for a while.


Try spying on your system with iotop and such tools to see what the 
server is actually doing
during the first query. If there is a spike in disk-IO then you've found 
the cause;

the tuples where not in RAM.
You may also want to run an EXPLAIN to make sure that the fast queries 
are not purely the result

of some query-result cache.


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Filter certain range of IP address.

[vinny]

On 2017-04-07 17:29, David G. Johnston wrote:

On Fri, Apr 7, 2017 at 8:18 AM, Dinesh Chandra 12108
<dinesh.chan...@cyient.com> wrote:


Dear Vinny,

Thanks for your valuable replay.

but I need a select query, which select only that record which
starts from IP "172.23.110" only from below table.

xxx 172.23.110.175
yyy 172.23.110.178
zzz 172.23.110.177
aaa 172.23.110.176
bbb 172.23.111.180
ccc 172.23.115.26


​SELECT ... WHERE substring(ip_addr::text, 1, 10) = '172.23.110'

David J.
 ​


While it's certainly possible to do it with a substring(), I'd strongly 
advise against it,
for several reasons, but the main one is that it does not take into 
account what happens to the presentation of the IP address when cast to 
a string. There might be some conditions that cause it to render as 
'172.023.110' instead of '172.23.110' just like numbers can be rendered 
as '1.234,56' or '1,234.56' depending on locale, and that would break 
the functionality without throwing an error.


Generally speaking; if you find yourself using a substring() on a 
datatype other than a string,
you should check if there isn't an operator that already can do what you 
want to do. PostgreSQL has operators
to do all the basic things with the datatypes it supports, so you don't 
have to re-invent the wheel. :-)



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Filter certain range of IP address.

[vinny]

On 2017-04-07 16:13, Dinesh Chandra 12108 wrote:

Hi expert,

May I know how to select a range of IP address.

Example: I have number of different-2 IP's present in a table.

I HAVE TO SELECT ONLY THAT IP ADDRESS WHICH DOES NOT START FROM PREFIX
“172.23.110”.

Thanks in advance

REGARDS,

DINESH CHANDRA

|DATABASE ADMINISTRATOR (ORACLE/POSTGRESQL)| CYIENT LTD. NOIDA.

--

Mobile: +91-9953975849 | Ext 1078 |dinesh.chan...@cyient.com

Plot No. 7, NSEZ, Phase-II ,Noida-Dadri Road, Noida - 201 305,India.



If you store the ip address as the INET datatype then you can use the 
INET operators
to see if any arbitraty number of bits match, the first 3 bytes means 
the first 24 bits:



SELECT '172.23.110.55'::inet << '172.23.110.1/24'::inet;
 ?column?
--
 t
(1 row)


SELECT '272.23.110.55'::inet << '172.23.110.1/24'::inet;
 ?column?
--
 f
(1 row)

See also: https://www.postgresql.org/docs/9.3/static/functions-net.html


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] How Can I check PostgreSQL backup is successfully or not ?

[vinny]

On 2017-02-27 14:29, John Gorman wrote:

Even though it's not listed in any of the documentation or “pg_dump
--help” you can check the return code of the process. A return code
greater than 0 (zero) usually indicates a failure

./bin >pg_dump -U dummy_user  dummy_database; echo $?

1

FROM: pgsql-performance-ow...@postgresql.org
[mailto:pgsql-performance-ow...@postgresql.org] ON BEHALF OF Rick
Otten
SENT: Monday, February 27, 2017 3:36 AM
TO: Dinesh Chandra 12108
CC: pgsql-performance@postgresql.org
SUBJECT: Re: [PERFORM] How Can I check PostgreSQL backup is
successfully or not ?

Although it doesn't really tell if the pg_dump was successful (you'll
need to do a full restore to be sure), I generate an archive list.  If
that fails, the backup clearly wasn't successful, and if it succeeds,
odds are pretty good that it worked:

On Mon, Feb 27, 2017 at 4:35 AM, Dinesh Chandra 12108
 wrote:

Hi,

We are taking daily full backup of PostgreSQL database using PG_DUMP
which is automatic scheduled through Cronjobs.

How can I check my yesterday backup is successfully or not?

Is there any query or view by which I can check it?

REGARDS,

DINESH CHANDRA

|DATABASE ADMINISTRATOR (ORACLE/POSTGRESQL)| CYIENT LTD. NOIDA.



It's important to note the distinction between

"the backup process did not fail"

and

"we now have a trustworthy backup"

And you can go full-paranoia and say that you can successfully create a 
perfectly working backup of the wrong database.


So what is it that you want to make sure of:
1. Did the process give an error?
2. Did the process create a usable backup?

What are the chances of #1 reporting success but still producing a bad 
backup?

And can #2 fail on a good database, and if so, can you detect that?




--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] DO I miss something ?

[vinny]

On 2016-11-18 14:52, Metatrader EA wrote:

Hi,

Do I miss something?
Shouldn't I have some rows from this query ?

Why is it empty?

SELECT relname, idx_tup_fetch + seq_tup_read as TotalReads from
pg_stat_all_tables
 WHERE idx_tup_fetch + seq_tup_read != 0
 order by TotalReads desc
 LIMIT 10;
 relname | totalreads
-+
(0 rows)

//Bill



Is statistics collection enabled in the config?

See: https://www.postgresql.org/docs/9.5/static/monitoring-stats.html


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Sql Query :: Any advice ?

[vinny]

Are the forex and options in the hist_account_balance table?
The sequential scan is on that table so if they are,
so I'm guessing they should probably by in the index.

On 2016-11-15 15:30, Henrik Ekenberg wrote:

Here are the indexes I have for those queries

Indexes:

hist_account_balance  :: "hist_account_balance_ix1" btree (trade_no)

trades :: "trades_pkey" PRIMARY KEY, btree  (trade_no)
 "trades_trade_date_index" btree (trade_date)

//H

Quoting vinny <vi...@xs4all.nl>:


On 2016-11-15 14:27, Henrik Ekenberg wrote:


Hi,

I have some data to join and I want to get som advice from you.

Any tips ? Any comments are apreciated

//H

select trade_no
from
forecast_trades.hist_account_balance
left join trades using (trade_no)
where  trade_date > current_date - 120
andtrade_date < current_date - 30
andforex = 'f'
andoptions = 'f'
group by trade_no
having max(account_size) > 0
;

( Query Plan : https://explain.depesz.com/s/4lOD )

QUERY PLAN





--

HashAggregate  (cost=34760605.76..34773866.26 rows=1060840
width=15)
(actual time=1142816.632..1150194.076 rows=2550634 loops=1)
Group Key: hist_account_balance.trade_no
Filter: (max(hist_account_balance.account_size) > 0::numeric)
Rows Removed by Filter: 18240023
->  Hash Join  (cost=3407585.35..34530512.29 rows=46018694
width=15) (actual time=60321.201..1108647.151 rows=44188963
loops=1)
Hash Cond: (hist_account_balance.trade_no =
trades.trade_no)
->  Seq Scan on hist_account_balance
(cost=0.00..14986455.20
rows=570046720 width=15) (actual time=0.016..524427.140
rows=549165594
loops=1)
->  Hash  (cost=3159184.13..3159184.13 rows=19872098
width=12) (actual time=60307.001..60307.001 rows=20790658 loops=1)
Buckets: 2097152  Batches: 1  Memory Usage: 913651kB
->  Index Scan using trades_trade_date_index on
trades
(cost=0.58..3159184.13 rows=19872098 width=12) (actual
time=0.078..52213.976 rows=20790658 loops=1)
Index Cond: ((trade_date >
(('now'::cstring)::date - 120)) AND (trade_date <
(('now'::cstring)::date - 30)))
Filter: ((NOT forex) AND (NOT options))
Rows Removed by Filter: 2387523
Planning time: 2.157 ms
Execution time: 1151234.290 ms
(15 rows)

What kind of indexes have you created for those tables?

--
Sent via pgsql-performance mailing list
(pgsql-performance@postgresql.org)
To make changes to your
subscription:http://www.postgresql.org/mailpref/pgsql-performance



--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Sql Query :: Any advice ?

[vinny]

On 2016-11-15 14:27, Henrik Ekenberg wrote:

Hi,

I have some data to join and I want to get som advice from you.

Any tips ? Any comments are apreciated

//H

select trade_no
from
forecast_trades.hist_account_balance
left join trades using (trade_no)
where  trade_date > current_date - 120
   andtrade_date < current_date - 30
   andforex = 'f'
   andoptions = 'f'
   group by trade_no
   having max(account_size) > 0
;

( Query Plan : https://explain.depesz.com/s/4lOD )

QUERY PLAN

--
 HashAggregate  (cost=34760605.76..34773866.26 rows=1060840 width=15)
(actual time=1142816.632..1150194.076 rows=2550634 loops=1)
   Group Key: hist_account_balance.trade_no
   Filter: (max(hist_account_balance.account_size) > 0::numeric)
   Rows Removed by Filter: 18240023
   ->  Hash Join  (cost=3407585.35..34530512.29 rows=46018694
width=15) (actual time=60321.201..1108647.151 rows=44188963 loops=1)
 Hash Cond: (hist_account_balance.trade_no = trades.trade_no)
 ->  Seq Scan on hist_account_balance  (cost=0.00..14986455.20
rows=570046720 width=15) (actual time=0.016..524427.140 rows=549165594
loops=1)
 ->  Hash  (cost=3159184.13..3159184.13 rows=19872098
width=12) (actual time=60307.001..60307.001 rows=20790658 loops=1)
   Buckets: 2097152  Batches: 1  Memory Usage: 913651kB
   ->  Index Scan using trades_trade_date_index on trades
(cost=0.58..3159184.13 rows=19872098 width=12) (actual
time=0.078..52213.976 rows=20790658 loops=1)
 Index Cond: ((trade_date >
(('now'::cstring)::date - 120)) AND (trade_date <
(('now'::cstring)::date - 30)))
 Filter: ((NOT forex) AND (NOT options))
 Rows Removed by Filter: 2387523
 Planning time: 2.157 ms
 Execution time: 1151234.290 ms
(15 rows)



What kind of indexes have you created for those tables?


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance