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

[Moreno Andreo]

  
  
Il 07/04/2017 17:56, Rick Otten ha
  scritto:


  

  On Fri, Apr 7, 2017 at 11:29 AM,
David G. Johnston 
wrote:

  
On Fri, Apr 7,
2017 at 8:18 AM, Dinesh Chandra 12108  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'
  

  



or
   select ... where ip_addr
  << '172.23.110/32';
  

  

/32 is for one address only (fourth byte, which we want to exclude),
so we need to use /24 (as for CIDR notation), that would be equal to
a 255.255.255.0 subnet mask.

My 2 cents
Moreno


  

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

[Dinesh Chandra 12108]

Thanks.

It’s working fine.
Thank you so much

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.

From: Rick Otten [mailto:rottenwindf...@gmail.com]
Sent: 07 April, 2017 9:26 PM
To: David G. Johnston 
Cc: Dinesh Chandra 12108 ; vinny ; 
pgsql-performance-ow...@postgresql.org; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Filter certain range of IP address.



On Fri, Apr 7, 2017 at 11:29 AM, David G. Johnston 
> wrote:
On Fri, Apr 7, 2017 at 8:18 AM, Dinesh Chandra 12108 
> 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'

or
   select ... where ip_addr << '172.23.110/32';

if ip_addr is an inet data type -- 
https://www.postgresql.org/docs/9.6/static/functions-net.html

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

[Rick Otten]

On Fri, Apr 7, 2017 at 11:29 AM, David G. Johnston <
david.g.johns...@gmail.com> 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'
>

or
   select ... where ip_addr << '172.23.110/32';

if ip_addr is an inet data type -- https://www.postgresql.org/
docs/9.6/static/functions-net.html

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

[David G. Johnston]

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.
 ​

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

[Dinesh Chandra 12108]

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

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.

-Original Message-
From: vinny [mailto:vi...@xs4all.nl] 
Sent: 07 April, 2017 7:52 PM
To: Dinesh Chandra 12108 
Cc: pgsql-performance-ow...@postgresql.org; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Filter certain range of IP address.

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] Understanding PostgreSQL query execution time

[Gerardo Herzig]

- Mensaje original -
> De: "Haider Ali" 
> Para: pgsql-performance@postgresql.org
> Enviados: Viernes, 7 de Abril 2017 11:56:53
> Asunto: [PERFORM] Understanding PostgreSQL query execution time
> 
> 
> 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
> 
> 
> SELECT "global_configs".* FROM "global_configs" ORDER BY
> "global_configs"."id" ASC LIMIT $1
> 
> 
> 
> 
> executed multiple times instantaneous one after another have
> following execution time
> 
> 
> 1st time => 147.5ms
> 
> 2nd time => 3.0ms
> 
> 3rd time => 3.0ms
> 
> 4th time => 3.0ms
> 
> 5th time => 0.8ms

That is the effects of the postgres/Linux cache for shure. 
> 
> 
> I want to understand why there is a huge time difference between 1st
> and rest of the executions.
> 
> 
> Relation to other problem
> 
> 
> 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)
> 
> 
> Questions
> 
> 
> 
> 
> 1. Why first query on a table takes more time then queries
> followed by it ?
> 2. Why above behaviour doesn't reflect on Amazon RDS ?
> 
Amazon provides you with SSD like disks, running close to memory speed. That 
would explain the little impact of having a ram cache.

HTH
Gerardo
> 
> Haider Ali


-- 
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

[l...@laurent-hasson.com]

The first behavior is very likely just caching. The plan and results from the 
query are cached, so the second time, it's reused directly.

If you ran a bunch of other queries in the middle and effectively exhausted the 
cache, then back to your query, likely tou'd see the 'slow' behavior again.

As for AWS, not sure, but likely about memory and config more than latency.


Sent from my BlackBerry 10 smartphone.
From: Haider Ali
Sent: Friday, April 7, 2017 09:58
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Understanding PostgreSQL query execution time


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

SELECT  "global_configs".* FROM "global_configs" ORDER BY "global_configs"."id" 
ASC LIMIT $1

executed multiple times instantaneous one after another have following 
execution time

1st time => 147.5ms
2nd time => 3.0ms
3rd time => 3.0ms
4th time => 3.0ms
5th time => 0.8ms

I want to understand why there is a huge time difference between 1st and rest 
of the executions.

Relation to other problem

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)

Questions


  1.  Why first query on a table takes more time then queries followed by it ?
  2.  Why above behaviour doesn't reflect on Amazon RDS ?

Thank you for reading my post.

--
Haider Ali

[PERFORM] Understanding PostgreSQL query execution time

[Haider Ali]

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

*SELECT  "global_configs".* FROM "global_configs" ORDER BY
"global_configs"."id" ASC LIMIT $1*

executed multiple times instantaneous one after another have following
execution time

1st time => *147.5ms*
*2nd time => 3.0ms*
*3rd time => 3.0ms*
*4th time => 3.0ms*
*5th time => 0.8ms*

I want to understand why there is a huge time difference between 1st and
rest of the executions.

*Relation to other problem*

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)

*Questions*


   1. Why first query on a table takes more time then queries followed by
   it ?
   2. Why above behaviour doesn't reflect on Amazon RDS ?


Thank you for reading my post.

-- 
Haider Ali

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

[PERFORM] Filter certain range of IP address.

[Dinesh Chandra 12108]

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.