Re: missing FROM-clause entry for table bbbb

[David G. Johnston]

You should pick one list to post to.

On Tue, Jan 16, 2018 at 4:36 AM, Abhra Kar  wrote:

> Hi
>  I tried to modify below sql command in postgres syntax---
>
>  insert into  (id, groupid, ele_id, ro_element_id) ")
>

​That ") looks wrong...​

 select .nextval, :groupid, gosp.neteleid, gosp.hodev
>from net_ele gos, net_gos_prop gosp "
>

​As does that "
​

>where gos.eid in ( :eids ) and gos.id = gosp.net_element_id
> and
> gos.id not in (select ealjs.element_id from  ealjs where
> ealjs.groupid = :groupid)
>
> If I run this it gives error for ":".If I remove : then I will get error
> like  missing FROM-clause entry for table .
>
>
​How are you executing the query, in what client program.  If it is ​psql
did you define the variable groupid and eids prior to running the provided
query?

What is the issue here and what should be exact query in postgressql
>
>
​PostgreSQL

You should provide the exact and complete psql script you are trying to run
... asking for help with an incomplete example won't get you far.

David J.
​

Re: missing FROM-clause entry for table bbbb

[David Rowley]

On 17 January 2018 at 00:36, Abhra Kar  wrote:
>  insert into  (id, groupid, ele_id, ro_element_id) ")
>  select .nextval, :groupid, gosp.neteleid, gosp.hodev
>from net_ele gos, net_gos_prop gosp "
>where gos.eid in ( :eids ) and gos.id = gosp.net_element_id and
> gos.id not in (select ealjs.element_id from  ealjs where
> ealjs.groupid = :groupid)
>
> If I run this it gives error for ":".If I remove : then I will get error
> like  missing FROM-clause entry for table .

.nextval looks like some sort of Oracle syntax to get the nextval
from a sequence. In PostgreSQL you'd do nextval(''); Assuming you
have a sequence named ""

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

SSD filesystem aligned to DBMS

[Neto pr]

Hi all

Sorry, but I'm not sure that this doubt is appropriate for this list, but I
do need to prepare the file system of an SSD disk in a way that pointed me
to, which would be a way optimized SSD
 to work. I have a disk: SSD: Samsung 500 GB SATA III 6Gb/s - Model: 850 Evo
http://www.samsung.com/semiconductor/minisite/ssd/product/consumer/850evo/

One person on the list me said that should be partition aligned to 3072 not
default 2048, to start on erase block bounduary. And fs block should be 8kb.

Can you give me a hint of what program I could do this. I have already used
fdisk but I do not know how to do this in Fdisk. I used Linux Debian
8(Jessie) 64b with Ext4 File system.

If you prefer, just reply to me, since the subject would not be about
postgresql itself. netopr...@gmail.com

Best Regards
Neto

Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Adam Sjøgren]

We are seeing these "ERROR:  unexpected chunk number 0 (expected 1) for
toast value 1498303849 in pg_toast_10919630" in increasing numbers again¹.

An observation is that they seem to only happen for tsvector fields.

Here is an example sequence of queries for a record (we have more than a
handful of these currently), which exhibits the problem.

First we get two other fields, 'sequence' is large enough to be toast'ed:

  2018-01-16 08:51:17.362 efam=# select id,sequence from efam.sequence where id 
= 164504550;
  Time: 1.150 ms

No problem.

Then we also fetch the tsvector field:

  2018-01-16 08:51:27.773 efam=# select id,sequence,fts from efam.sequence 
where id = 164504550;
  ERROR:  unexpected chunk number 0 (expected 1) for toast value 1498303849 in 
pg_toast_10919630
  Time: 0.912 ms

And we get the error.

Getting the id and the tsvector:

  2018-01-16 08:51:34.174 efam=# select id,fts from efam.sequence where id = 
164504550;
  ERROR:  unexpected chunk number 0 (expected 1) for toast value 1498303849 in 
pg_toast_10919630
  Time: 6.138 ms

gives the error.

Just getting the tsvector:

  2018-01-16 08:51:40.066 efam=# select fts from efam.sequence where id = 
164504550;
  ERROR:  unexpected chunk number 0 (expected 1) for toast value 1498303849 in 
pg_toast_10919630
  Time: 1.805 ms

Gives the error.

Field definitions:

   id| integer | 
   sequence  | text| 
   fts   | tsvector| 

Maybe the observation that this only happens (for us) on tsvector rings
a bell for someone?


  Best regards,

Adam


¹ As reported back in June, 2017, starting here:
  https://www.postgresql.org/message-id/7pefuv53dl.fsf%40novozymes.com
  (I have to admit I never got around to trying to revert the commit
  Alvaro Herrera suggested we try without
  
(https://www.postgresql.org/message-id/20170611033840.hruqadsk47qcdrqb%40alvherre.pgsql))

-- 
 "No more than that, but very powerful all theAdam Sjøgren
  same; simple things are good."a...@novozymes.com

missing FROM-clause entry for table bbbb

[Abhra Kar]

Hi
 I tried to modify below sql command in postgres syntax---

 insert into  (id, groupid, ele_id, ro_element_id) ")
 select .nextval, :groupid, gosp.neteleid, gosp.hodev
   from net_ele gos, net_gos_prop gosp "
   where gos.eid in ( :eids ) and gos.id = gosp.net_element_id and
gos.id not in (select ealjs.element_id from  ealjs where
ealjs.groupid = :groupid)

If I run this it gives error for ":".If I remove : then I will get error
like  missing FROM-clause entry for table .

What is the issue here and what should be exact query in postgressql

Thanks

Re: SSD filesystem aligned to DBMS

[Scott Marlowe]

On Tue, Jan 16, 2018 at 7:47 AM, Neto pr  wrote:
> Hi all
>
> Sorry, but I'm not sure that this doubt is appropriate for this list, but I
> do need to prepare the file system of an SSD disk in a way that pointed me
> to, which would be a way optimized SSD
>  to work. I have a disk: SSD: Samsung 500 GB SATA III 6Gb/s - Model: 850 Evo
> http://www.samsung.com/semiconductor/minisite/ssd/product/consumer/850evo/
>
> One person on the list me said that should be partition aligned to 3072 not
> default 2048, to start on erase block bounduary. And fs block should be 8kb.
>
> Can you give me a hint of what program I could do this. I have already used
> fdisk but I do not know how to do this in Fdisk. I used Linux Debian
> 8(Jessie) 64b with Ext4 File system.

fdisk is pretty old and can't handle larger disks. You can get a fair
bit of control over the process with parted, but it takes some getting
used to. As far as I know, linux's ext4 has a maximum block size of
4k. I can't imagine alignment matters to SSDs and I would take any
advice as such with a large grain of salt and then if I had questions
about performance I'd test it to see. I'm willing to bet a couple
bucks it makes ZERO difference.

>
> If you prefer, just reply to me, since the subject would not be about
> postgresql itself. netopr...@gmail.com

No this affects everybody who uses SSDs so let's keep it on list if we can.

OPtimize the performance of a query

[hmidi slim]

Hi,
I have two tables in the same database: geoname and test_table.
The geoname table contains many columns which are: name, feature_class,
feature_code, admin1, admin2,admin3, name and so on.
The second table 'test_table' contains only the columns: city, state.
There is no join between the two tables and I want to make a match between
the data contained in each of them because I need the result for a farther
process.
I wrote this query:
select g.name, t.city
from geoname as g, test_table as t
where (lower(g.feature_class) like 'P' or lower(g.feature_class) like 'L')
and lower(g.country_code) like 'US'
and lower(g.admin1) like lower(t.state)
and (lower(g.name) like lower(t.city) or lower(g.name) like lower(t.city ||
'city'))


The table geoname contains 370260 rows and the table test_table contains
10270 rows.
The query took a long time to accomplish more than half an hour.Should I
add another column in the table test_table which contains the country_code
and make an inner join with the geoname table or should I use indexs to
accelerate the process?

Re: OPtimize the performance of a query

[hmidi slim]

Sorry I forget the lower command when I wrote the code, it is like this:
lower(g.country_code) like lower('US')
(lower(g.feature_class) like lowwer('P') or lower(g.feature_class) like
lower('L'))


2018-01-16 17:40 GMT+01:00 Martin Moore :

>
>
> >Hi,
>
> >I have two tables in the same database: geoname and test_table.
>
> >The geoname table contains many columns which are: name, feature_class,
> feature_code, admin1, admin2,admin3, name and so on.
>
> >The second table 'test_table' contains only the columns: city, state.
>
> >There is no join between the two tables and I want to make a match
> between the data contained in each of them because I need the result for a
> farther process.
>
> >I wrote this query:
>
> >select g.name, t.city
>
> >from geoname as g, test_table as t
>
> >where (lower(g.feature_class) like 'P' or lower(g.feature_class) like 'L')
>
> >and lower(g.country_code) like 'US'
>
> >and lower(g.admin1) like lower(t.state)
>
> >and (lower(g.name) like lower(t.city) or lower(g.name) like lower(t.city
> || 'city'))
>
> >The table geoname contains 370260 rows and the table test_table contains
> 10270 rows.
>
> >The query took a long time to accomplish more than half an hour.Should I
> add another column in the table test_table which contains the country_code
> and make an inner join with the geoname table or >should I use indexs to
> accelerate the process?
>
>
>
> Indexes are your friends ☺
>
>
>
> I’d certainly add indexes on lower(g.feature_class, g.country_code)  and
> lower(t.state)
>
>
>
> Note “and lower(g.country_code) like 'US'” will not return any results as
> you’ve got the ‘US’ in UPPER…. Same as (lower(g.feature_class) like 'P' or
> lower(g.feature_class) like 'L')
>
>
>
> Why are you using LIKE? Equals (=) is surely correct and probably faster?
>
>
>
>
>
> Martin.
>

Re: OPtimize the performance of a query

[James Keener]

Do you have any indecies?
https://www.postgresql.org/docs/current/static/indexes-expressional.html
might be helpful to you.

Also, EXPLAIN will help you understand how your query is being run and
where it can be improved.

https://www.postgresql.org/docs/current/static/using-explain.html
http://postgresguide.com/performance/explain.html
http://jimkeener.com/posts/explain-pg

Jim

On Tue, Jan 16, 2018 at 11:32 AM, hmidi slim  wrote:

> Hi,
> I have two tables in the same database: geoname and test_table.
> The geoname table contains many columns which are: name, feature_class,
> feature_code, admin1, admin2,admin3, name and so on.
> The second table 'test_table' contains only the columns: city, state.
> There is no join between the two tables and I want to make a match between
> the data contained in each of them because I need the result for a farther
> process.
> I wrote this query:
> select g.name, t.city
> from geoname as g, test_table as t
> where (lower(g.feature_class) like 'P' or lower(g.feature_class) like 'L')
> and lower(g.country_code) like 'US'
> and lower(g.admin1) like lower(t.state)
> and (lower(g.name) like lower(t.city) or lower(g.name) like lower(t.city
> || 'city'))
>
>
> The table geoname contains 370260 rows and the table test_table contains
> 10270 rows.
> The query took a long time to accomplish more than half an hour.Should I
> add another column in the table test_table which contains the country_code
> and make an inner join with the geoname table or should I use indexs to
> accelerate the process?
>

Re: OPtimize the performance of a query

[Melvin Davidson]

On Tue, Jan 16, 2018 at 11:46 AM, James Keener  wrote:

> Do you have any indecies? https://www.postgresql.org/
> docs/current/static/indexes-expressional.html might be helpful to you.
>
> Also, EXPLAIN will help you understand how your query is being run and
> where it can be improved.
>
> https://www.postgresql.org/docs/current/static/using-explain.html
> http://postgresguide.com/performance/explain.html
> http://jimkeener.com/posts/explain-pg
>
> Jim
>
> On Tue, Jan 16, 2018 at 11:32 AM, hmidi slim 
> wrote:
>
>> Hi,
>> I have two tables in the same database: geoname and test_table.
>> The geoname table contains many columns which are: name, feature_class,
>> feature_code, admin1, admin2,admin3, name and so on.
>> The second table 'test_table' contains only the columns: city, state.
>> There is no join between the two tables and I want to make a match
>> between the data contained in each of them because I need the result for a
>> farther process.
>> I wrote this query:
>> select g.name, t.city
>> from geoname as g, test_table as t
>> where (lower(g.feature_class) like 'P' or lower(g.feature_class) like 'L')
>> and lower(g.country_code) like 'US'
>> and lower(g.admin1) like lower(t.state)
>> and (lower(g.name) like lower(t.city) or lower(g.name) like lower(t.city
>> || 'city'))
>>
>>
>> The table geoname contains 370260 rows and the table test_table contains
>> 10270 rows.
>> The query took a long time to accomplish more than half an hour.Should I
>> add another column in the table test_table which contains the country_code
>> and make an inner join with the geoname table or should I use indexs to
>> accelerate the process?
>>
>
>
It would have been more helpful if you had included
the actual table structures for both tables.
However, I would start by creating separate indexes on
lower(feature_class)
lower(country_code)
lower(admin1)
lower(name)
lower(city)

That being said, you are better off forcing lowercase on all fields
BEFORE inserting into the table.
EG:

INSERT INTO test_table VALUES (lower(some_key), lower(name),
lower(feature_class), )

Then you would would not need to use lower() in the indexes or the query.

Please, in the future, always include your version of PostgreSQL and O/S

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: OPtimize the performance of a query

[Alban Hertroys]

> On 16 Jan 2018, at 17:32, hmidi slim  wrote:
> 
> Hi,
> I have two tables in the same database: geoname and test_table.
> The geoname table contains many columns which are: name, feature_class, 
> feature_code, admin1, admin2,admin3, name and so on.
> The second table 'test_table' contains only the columns: city, state.
> There is no join between the two tables and I want to make a match between 
> the data contained in each of them because I need the result for a farther 
> process.
> I wrote this query:
> select g.name, t.city
> from geoname as g, test_table as t
> where (lower(g.feature_class) like 'P' or lower(g.feature_class) like 'L')
> and lower(g.country_code) like 'US'
> and lower(g.admin1) like lower(t.state)
> and (lower(g.name) like lower(t.city) or lower(g.name) like lower(t.city || 
> 'city'))

That query is equivalent to:

select g.name, t.city
from geoname as g, test_table as t
where false or lower(g.name) = lower(t.city || 'city'));

So those are probably not the results you want.

At the very least, if you're lower-casing column contents, don't compare those 
to an upper-cased constant ;)
Also, AND has precedence over OR, which is the other reason why my equivalent 
query is so much shorter.
And finally, LIKE is equivalent to = (equals) without any wildcards.

> The table geoname contains 370260 rows and the table test_table contains 
> 10270 rows.
> The query took a long time to accomplish more than half an hour.Should I add 
> another column in the table test_table which contains the country_code and 
> make an inner join with the geoname table or should I use indexs to 
> accelerate the process?

Some indices on lower(g.name) and lower(t.city) from your query would be 
useful, but in that case make sure you take the concatenation of 'city' out of 
the lower()-call in your query.

Just reading your latest addition - using lower() on constants is just a waste 
of cycles. It won't hurt your query much though.

Regards,

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: OPtimize the performance of a query

[Pavel Stehule]

Hi

2018-01-16 17:44 GMT+01:00 hmidi slim :

> Sorry I forget the lower command when I wrote the code, it is like this:
> lower(g.country_code) like lower('US')
> (lower(g.feature_class) like lowwer('P') or lower(g.feature_class) like
> lower('L'))
>

please, don't do top post.

Your query must be slow. When you use LIKE instead =. It is terrible
performance issue.

So don't use "LIKE" is first rule. Second - you can create functional
indexes

CREATE INDEX ON geoname ((lower(name)))

Regards

Pavel

>
>
> 2018-01-16 17:40 GMT+01:00 Martin Moore :
>
>>
>>
>> >Hi,
>>
>> >I have two tables in the same database: geoname and test_table.
>>
>> >The geoname table contains many columns which are: name, feature_class,
>> feature_code, admin1, admin2,admin3, name and so on.
>>
>> >The second table 'test_table' contains only the columns: city, state.
>>
>> >There is no join between the two tables and I want to make a match
>> between the data contained in each of them because I need the result for a
>> farther process.
>>
>> >I wrote this query:
>>
>> >select g.name, t.city
>>
>> >from geoname as g, test_table as t
>>
>> >where (lower(g.feature_class) like 'P' or lower(g.feature_class) like
>> 'L')
>>
>> >and lower(g.country_code) like 'US'
>>
>> >and lower(g.admin1) like lower(t.state)
>>
>> >and (lower(g.name) like lower(t.city) or lower(g.name) like
>> lower(t.city || 'city'))
>>
>> >The table geoname contains 370260 rows and the table test_table contains
>> 10270 rows.
>>
>> >The query took a long time to accomplish more than half an hour.Should I
>> add another column in the table test_table which contains the country_code
>> and make an inner join with the geoname table or >should I use indexs to
>> accelerate the process?
>>
>>
>>
>> Indexes are your friends ☺
>>
>>
>>
>> I’d certainly add indexes on lower(g.feature_class, g.country_code)  and
>> lower(t.state)
>>
>>
>>
>> Note “and lower(g.country_code) like 'US'” will not return any results as
>> you’ve got the ‘US’ in UPPER…. Same as (lower(g.feature_class) like 'P' or
>> lower(g.feature_class) like 'L')
>>
>>
>>
>> Why are you using LIKE? Equals (=) is surely correct and probably faster?
>>
>>
>>
>>
>>
>> Martin.
>>
>
>

Re: SSD filesystem aligned to DBMS

[Michael Loftis]

On Tue, Jan 16, 2018 at 08:02 Scott Marlowe  wrote:

> On Tue, Jan 16, 2018 at 7:47 AM, Neto pr  wrote:
> > Hi all
> >
> > Sorry, but I'm not sure that this doubt is appropriate for this list,
> but I
> > do need to prepare the file system of an SSD disk in a way that pointed
> me
> > to, which would be a way optimized SSD
> >  to work. I have a disk: SSD: Samsung 500 GB SATA III 6Gb/s - Model: 850
> Evo
> >
> http://www.samsung.com/semiconductor/minisite/ssd/product/consumer/850evo/
> >
> > One person on the list me said that should be partition aligned to 3072
> not
> > default 2048, to start on erase block bounduary. And fs block should be
> 8kb.
> >
> > Can you give me a hint of what program I could do this. I have already
> used
> > fdisk but I do not know how to do this in Fdisk. I used Linux Debian
> > 8(Jessie) 64b with Ext4 File system.
>
> fdisk is pretty old and can't handle larger disks. You can get a fair
> bit of control over the process with parted, but it takes some getting
> used to. As far as I know, linux's ext4 has a maximum block size of
> 4k. I can't imagine alignment matters to SSDs and I would take any
> advice as such with a large grain of salt and then if I had questions
> about performance I'd test it to see. I'm willing to bet a couple
> bucks it makes ZERO difference.


Alignment definitely makes a difference for writes. It can also make a
difference for random reads as well since the underlying read may not line
up to the hardware add in a read ahead (at drive or OS Level) and you’re
reading far more data in the drive than the OS asks for.

Stupidly a lot of this isn’t published by a lot of SSD manufacturers, but
through benchmarks it shows up.

Another potential difference here with SAS vs SATA is the maximum queue
depth supported by the protocol and drive.

SSD drives also do internal housekeeping tasks for wear leveling on writing.

I’ve seen SSD drives benchmark with 80-90MB sequential read or write,
change the alignment, and you’ll get 400+ on the same drive with sequential
reads (changing nothing else)

A specific example
https://www.servethehome.com/ssd-alignment-quickly-benchmark-ssd/


>
> >
> > If you prefer, just reply to me, since the subject would not be about
> > postgresql itself. netopr...@gmail.com
>
> No this affects everybody who uses SSDs so let's keep it on list if we can.
>
> --

"Genius might be described as a supreme capacity for getting its possessors
into trouble of all kinds."
-- Samuel Butler

Re: OPtimize the performance of a query

[hmidi slim]

I changed the operator like and I'm using the operator = .I got the results
much faster but I still have another question about operator. For
difference should I use '<>' or 'is distinct from' with indexes?

2018-01-16 17:49 GMT+01:00 Pavel Stehule :

> Hi
>
> 2018-01-16 17:44 GMT+01:00 hmidi slim :
>
>> Sorry I forget the lower command when I wrote the code, it is like this:
>> lower(g.country_code) like lower('US')
>> (lower(g.feature_class) like lowwer('P') or lower(g.feature_class) like
>> lower('L'))
>>
>
> please, don't do top post.
>
> Your query must be slow. When you use LIKE instead =. It is terrible
> performance issue.
>
> So don't use "LIKE" is first rule. Second - you can create functional
> indexes
>
> CREATE INDEX ON geoname ((lower(name)))
>
> Regards
>
> Pavel
>
>>
>>
>> 2018-01-16 17:40 GMT+01:00 Martin Moore :
>>
>>>
>>>
>>> >Hi,
>>>
>>> >I have two tables in the same database: geoname and test_table.
>>>
>>> >The geoname table contains many columns which are: name, feature_class,
>>> feature_code, admin1, admin2,admin3, name and so on.
>>>
>>> >The second table 'test_table' contains only the columns: city, state.
>>>
>>> >There is no join between the two tables and I want to make a match
>>> between the data contained in each of them because I need the result for a
>>> farther process.
>>>
>>> >I wrote this query:
>>>
>>> >select g.name, t.city
>>>
>>> >from geoname as g, test_table as t
>>>
>>> >where (lower(g.feature_class) like 'P' or lower(g.feature_class) like
>>> 'L')
>>>
>>> >and lower(g.country_code) like 'US'
>>>
>>> >and lower(g.admin1) like lower(t.state)
>>>
>>> >and (lower(g.name) like lower(t.city) or lower(g.name) like
>>> lower(t.city || 'city'))
>>>
>>> >The table geoname contains 370260 rows and the table test_table
>>> contains 10270 rows.
>>>
>>> >The query took a long time to accomplish more than half an hour.Should
>>> I add another column in the table test_table which contains the
>>> country_code and make an inner join with the geoname table or >should I use
>>> indexs to accelerate the process?
>>>
>>>
>>>
>>> Indexes are your friends ☺
>>>
>>>
>>>
>>> I’d certainly add indexes on lower(g.feature_class, g.country_code)  and
>>> lower(t.state)
>>>
>>>
>>>
>>> Note “and lower(g.country_code) like 'US'” will not return any results
>>> as you’ve got the ‘US’ in UPPER…. Same as (lower(g.feature_class) like 'P'
>>> or lower(g.feature_class) like 'L')
>>>
>>>
>>>
>>> Why are you using LIKE? Equals (=) is surely correct and probably faster?
>>>
>>>
>>>
>>>
>>>
>>> Martin.
>>>
>>
>>
>

Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Jorge Daniel]

Hi Adam , I've been seeing this same  kind of Error in my clusters for a while .

Is this running on postgresql 9.4.8? Because mine was fixed upgrading to 9.4.11 
, a bug indeed .



Kind regards



Jorge Daniel Fernandez



From: Adam Sjøgren 
Sent: Tuesday, January 16, 2018 7:18 AM
To: pgsql-gene...@postgresql.org
Subject: Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 
76753264 in pg_toast_10920100

We are seeing these "ERROR:  unexpected chunk number 0 (expected 1) for
toast value 1498303849 in pg_toast_10919630" in increasing numbers again¹.

An observation is that they seem to only happen for tsvector fields.

Here is an example sequence of queries for a record (we have more than a
handful of these currently), which exhibits the problem.

First we get two other fields, 'sequence' is large enough to be toast'ed:

  2018-01-16 08:51:17.362 efam=# select id,sequence from efam.sequence where id 
= 164504550;
  Time: 1.150 ms

No problem.

Then we also fetch the tsvector field:

  2018-01-16 08:51:27.773 efam=# select id,sequence,fts from efam.sequence 
where id = 164504550;
  ERROR:  unexpected chunk number 0 (expected 1) for toast value 1498303849 in 
pg_toast_10919630
  Time: 0.912 ms

And we get the error.

Getting the id and the tsvector:

  2018-01-16 08:51:34.174 efam=# select id,fts from efam.sequence where id = 
164504550;
  ERROR:  unexpected chunk number 0 (expected 1) for toast value 1498303849 in 
pg_toast_10919630
  Time: 6.138 ms

gives the error.

Just getting the tsvector:

  2018-01-16 08:51:40.066 efam=# select fts from efam.sequence where id = 
164504550;
  ERROR:  unexpected chunk number 0 (expected 1) for toast value 1498303849 in 
pg_toast_10919630
  Time: 1.805 ms

Gives the error.

Field definitions:

   id| integer |
   sequence  | text|
   fts   | tsvector|

Maybe the observation that this only happens (for us) on tsvector rings
a bell for someone?


  Best regards,

Adam


¹ As reported back in June, 2017, starting here:
  https://www.postgresql.org/message-id/7pefuv53dl.fsf%40novozymes.com
  (I have to admit I never got around to trying to revert the commit
  Alvaro Herrera suggested we try without
  
(https://www.postgresql.org/message-id/20170611033840.hruqadsk47qcdrqb%40alvherre.pgsql))

--
 "No more than that, but very powerful all theAdam Sjøgren
  same; simple things are good."a...@novozymes.com

Re: OPtimize the performance of a query

[Pavel Stehule]

Hi

2018-01-16 18:57 GMT+01:00 hmidi slim :

> I changed the operator like and I'm using the operator = .I got the
> results much faster but I still have another question about operator. For
> difference should I use '<>' or 'is distinct from' with indexes?
>

https://en.wikipedia.org/wiki/Posting_style#Top-posting .. please, don't do
it.

IS DISTINCT FROM has sense if your data - or your queries has NULL. If not,
and it is probably your case, then <> should be preferred.

Regards

Pavel



> 2018-01-16 17:49 GMT+01:00 Pavel Stehule :
>
>> Hi
>>
>> 2018-01-16 17:44 GMT+01:00 hmidi slim :
>>
>>> Sorry I forget the lower command when I wrote the code, it is like this:
>>> lower(g.country_code) like lower('US')
>>> (lower(g.feature_class) like lowwer('P') or lower(g.feature_class) like
>>> lower('L'))
>>>
>>
>> please, don't do top post.
>>
>> Your query must be slow. When you use LIKE instead =. It is terrible
>> performance issue.
>>
>> So don't use "LIKE" is first rule. Second - you can create functional
>> indexes
>>
>> CREATE INDEX ON geoname ((lower(name)))
>>
>> Regards
>>
>> Pavel
>>
>>>
>>>
>>> 2018-01-16 17:40 GMT+01:00 Martin Moore :
>>>


 >Hi,

 >I have two tables in the same database: geoname and test_table.

 >The geoname table contains many columns which are: name,
 feature_class, feature_code, admin1, admin2,admin3, name and so on.

 >The second table 'test_table' contains only the columns: city, state.

 >There is no join between the two tables and I want to make a match
 between the data contained in each of them because I need the result for a
 farther process.

 >I wrote this query:

 >select g.name, t.city

 >from geoname as g, test_table as t

 >where (lower(g.feature_class) like 'P' or lower(g.feature_class) like
 'L')

 >and lower(g.country_code) like 'US'

 >and lower(g.admin1) like lower(t.state)

 >and (lower(g.name) like lower(t.city) or lower(g.name) like
 lower(t.city || 'city'))

 >The table geoname contains 370260 rows and the table test_table
 contains 10270 rows.

 >The query took a long time to accomplish more than half an hour.Should
 I add another column in the table test_table which contains the
 country_code and make an inner join with the geoname table or >should I use
 indexs to accelerate the process?



 Indexes are your friends ☺



 I’d certainly add indexes on lower(g.feature_class, g.country_code)
 and lower(t.state)



 Note “and lower(g.country_code) like 'US'” will not return any results
 as you’ve got the ‘US’ in UPPER…. Same as (lower(g.feature_class) like 'P'
 or lower(g.feature_class) like 'L')



 Why are you using LIKE? Equals (=) is surely correct and probably
 faster?





 Martin.

>>>
>>>
>>
>

Re: SSD filesystem aligned to DBMS

[Neto pr]

2018-01-16 8:50 GMT-08:00 Michael Loftis :

>
> On Tue, Jan 16, 2018 at 08:02 Scott Marlowe 
> wrote:
>
>> On Tue, Jan 16, 2018 at 7:47 AM, Neto pr  wrote:
>> > Hi all
>> >
>> > Sorry, but I'm not sure that this doubt is appropriate for this list,
>> but I
>> > do need to prepare the file system of an SSD disk in a way that pointed
>> me
>> > to, which would be a way optimized SSD
>> >  to work. I have a disk: SSD: Samsung 500 GB SATA III 6Gb/s - Model:
>> 850 Evo
>> > http://www.samsung.com/semiconductor/minisite/ssd/
>> product/consumer/850evo/
>> >
>> > One person on the list me said that should be partition aligned to 3072
>> not
>> > default 2048, to start on erase block bounduary. And fs block should be
>> 8kb.
>> >
>> > Can you give me a hint of what program I could do this. I have already
>> used
>> > fdisk but I do not know how to do this in Fdisk. I used Linux Debian
>> > 8(Jessie) 64b with Ext4 File system.
>>
>> fdisk is pretty old and can't handle larger disks. You can get a fair
>> bit of control over the process with parted, but it takes some getting
>> used to. As far as I know, linux's ext4 has a maximum block size of
>> 4k. I can't imagine alignment matters to SSDs and I would take any
>> advice as such with a large grain of salt and then if I had questions
>> about performance I'd test it to see. I'm willing to bet a couple
>> bucks it makes ZERO difference.
>
>
> Alignment definitely makes a difference for writes. It can also make a
> difference for random reads as well since the underlying read may not line
> up to the hardware add in a read ahead (at drive or OS Level) and you’re
> reading far more data in the drive than the OS asks for.
>
> Stupidly a lot of this isn’t published by a lot of SSD manufacturers, but
> through benchmarks it shows up.
>
> Another potential difference here with SAS vs SATA is the maximum queue
> depth supported by the protocol and drive.
>
> SSD drives also do internal housekeeping tasks for wear leveling on
> writing.
>
> I’ve seen SSD drives benchmark with 80-90MB sequential read or write,
> change the alignment, and you’ll get 400+ on the same drive with sequential
> reads (changing nothing else)
>
> A specific example https://www.servethehome.com/ssd-
> alignment-quickly-benchmark-ssd/
>
>

Hi all
Searching I checked that In past, proper alignment required manual
calculation and intervention when partitioning. Many of the common
partition tools now handle partition alignment automatically.
For sample,  on an already partitioned disk, you can use *parted* (
https://wiki.archlinux.org/index.php/GNU_Parted#Check_alignment  )
to verify the alignment of a partition on a device in LInux S.O.  This
example I ran i my Samsung SSD 500GB 850 Evo, see below:

---BEGIN PARTED TOOL
-
root@hp2ml110deb:parted /dev/sdb
(parted) print list
Model: ATA Samsung SSD 850 (scsi)
Disk /dev/sdb: 500GB
Sector size (logical/physical): 512B/512B
Partition Table: loop
Disk Flags:

Number  Start  EndSize   File system  Flags
 1  0.00B  500GB  500GB  ext4

Model: ATA MB1000GCWCV (scsi)
Disk /dev/sda: 1000GB
Sector size (logical/physical): 512B/512B
Partition Table: gpt
Disk Flags:

Number  Start   End SizeFile system Name  Flags
 1  1049kB  538MB   537MB   fat32 boot, esp
 2  538MB   992GB   991GB   ext4
 3  992GB   1000GB  8319MB  linux-swap(v1)

(parted) select /dev/sdb
Using /dev/sdb
(parted) align-check
alignment type(min/opt)  [optimal]/minimal? opt
Partition number? 1
1 aligned
(parted)
 END
--

Regards
Neto





>
>>
>> >
>> > If you prefer, just reply to me, since the subject would not be about
>> > postgresql itself. netopr...@gmail.com
>>
>> No this affects everybody who uses SSDs so let's keep it on list if we
>> can.
>>
>> --
>
> "Genius might be described as a supreme capacity for getting its possessors
> into trouble of all kinds."
> -- Samuel Butler
>

Re: OPtimize the performance of a query

[hmidi slim]

Thank you for your advices and thanks for all people who give me some best
practises and useful ideas.

Re: OPtimize the performance of a query

[Pavel Stehule]

2018-01-16 19:35 GMT+01:00 hmidi slim :

> Thank you for your advices and thanks for all people who give me some best
> practises and useful ideas.
>

you are welcome

Regards

Pavel

Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Adam Sjøgren]

  Hi Jorge,


This sounds very interesting - we are running PostgreSQL 9.3.20.

Did you ever find out exactly what the change that solved the problem
between 9.4.8 and 9.4.11 was?


  Best regards,

Adam


Jorge writes:

> Hi Adam , I've been seeing this same  kind of Error in my clusters for a 
> while .
>
> Is this running on postgresql 9.4.8? Because mine was fixed upgrading to 
> 9.4.11 , a bug indeed .
>
>
>
> Kind regards
>
>
>
> Jorge Daniel Fernandez
>
>
> 
> From: Adam Sjøgren 
> Sent: Tuesday, January 16, 2018 7:18 AM
> To: pgsql-gene...@postgresql.org
> Subject: Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 
> 76753264 in pg_toast_10920100
>
> We are seeing these "ERROR:  unexpected chunk number 0 (expected 1) for
> toast value 1498303849 in pg_toast_10919630" in increasing numbers again¹.
>
> An observation is that they seem to only happen for tsvector fields.
>
> Here is an example sequence of queries for a record (we have more than a
> handful of these currently), which exhibits the problem.
>
> First we get two other fields, 'sequence' is large enough to be toast'ed:
>
>   2018-01-16 08:51:17.362 efam=# select id,sequence from efam.sequence where 
> id = 164504550;
>   Time: 1.150 ms
>
> No problem.
>
> Then we also fetch the tsvector field:
>
>   2018-01-16 08:51:27.773 efam=# select id,sequence,fts from efam.sequence 
> where id = 164504550;
>   ERROR:  unexpected chunk number 0 (expected 1) for toast value 1498303849 
> in pg_toast_10919630
>   Time: 0.912 ms
>
> And we get the error.
>
> Getting the id and the tsvector:
>
>   2018-01-16 08:51:34.174 efam=# select id,fts from efam.sequence where id = 
> 164504550;
>   ERROR:  unexpected chunk number 0 (expected 1) for toast value 1498303849 
> in pg_toast_10919630
>   Time: 6.138 ms
>
> gives the error.
>
> Just getting the tsvector:
>
>   2018-01-16 08:51:40.066 efam=# select fts from efam.sequence where id = 
> 164504550;
>   ERROR:  unexpected chunk number 0 (expected 1) for toast value 1498303849 
> in pg_toast_10919630
>   Time: 1.805 ms
>
> Gives the error.
>
> Field definitions:
>
>id| integer |
>sequence  | text|
>fts   | tsvector|
>
> Maybe the observation that this only happens (for us) on tsvector rings
> a bell for someone?
>
>
>   Best regards,
>
> Adam
>
>
> ¹ As reported back in June, 2017, starting here:
>   https://www.postgresql.org/message-id/7pefuv53dl.fsf%40novozymes.com
>   (I have to admit I never got around to trying to revert the commit
>   Alvaro Herrera suggested we try without
>   
> (https://www.postgresql.org/message-id/20170611033840.hruqadsk47qcdrqb%40alvherre.pgsql))
>
> --
>  "No more than that, but very powerful all theAdam Sjøgren
>   same; simple things are good."a...@novozymes.com
>
>

-- 
 "No more than that, but very powerful all theAdam Sjøgren
  same; simple things are good."a...@novozymes.com

Re: OPtimize the performance of a query

[Gavin Flower]

Hi Hmidi,

On 17/01/18 06:57, hmidi slim wrote:
I changed the operator like and I'm using the operator = .I got the 
results much faster but I still have another question about operator. 
For difference should I use '<>' or 'is distinct from' with indexes?


2018-01-16 17:49 GMT+01:00 Pavel Stehule >:


Hi

2018-01-16 17:44 GMT+01:00 hmidi slim mailto:hmidi.sl...@gmail.com>>:

Sorry I forget the lower command when I wrote the code, it is
like this: lower(g.country_code) like lower('US')
(lower(g.feature_class) like lowwer('P') or
lower(g.feature_class) like lower('L'))


please, don't do top post.


[...]

Top posting is when you reply at the top of the email, rather than at 
the bottom like this.


Bottom posting allows people to see the context before your reply.  You 
can trim excess, or no longer relevant, content - but note the bits that 
you have omitted with '[...]'


Bottom posting is preferred.


Cheers,
Gavin

Re: SSD filesystem aligned to DBMS

[George Neuner]

On Tue, 16 Jan 2018 16:50:28 +, Michael Loftis 
wrote:

>Alignment definitely makes a difference for writes. It can also make a
>difference for random reads as well since the underlying read may not line
>up to the hardware add in a read ahead (at drive or OS Level) and you’re
>reading far more data in the drive than the OS asks for.

Best performance will be when the filesystem block size matches the
SSD's writeable *data* block size.  The SSD also has a separate erase
sector size which is some (large) multiple of the data block size.



Recall that an SSD doesn't overwrite existing data blocks.  When you
update a file, the updates are written out to *new* "clean" data
blocks, and the file's block index is updated to reflect the new
structure.  

The old data blocks are marked "free+dirty".  They must be erased
(become "free+clean") before reuse.  Depending on the drive size, the
SSD's erase sectors may be anywhere from 64MB..512MB in size, and so a
single erase sector will hold many individually writeable data blocks.

When an erase sector is cleaned, ALL the data blocks it contains are
erased.  If any still contain good data, they must be relocated before
the erase can be done.



You don't want your filesystem block to be smaller than the SSD data
block, because then you are subject to *unnecessary* write
applification: the drive controller has to read/modify/write a whole
data block to change any part of it.

But, conversely, filesystem blocks that are larger than the SSD write
block typically are not a problem because ... unless you do something
really stupid [with really low level code] ... the large filesystem
blocks will end up be an exact multiple of data blocks.


Much of the literature re: alignment actually is related to the erase
sectors rather than the data blocks and is targeted at embedded
systems that are not using conventional filesystems but rather are
accessing the raw SSD.

You do want your partitions to start on erase sector boundaries, but
that usually is trivial to do.


>Stupidly a lot of this isn’t published by a lot of SSD manufacturers, but
>through benchmarks it shows up.

Yes.  The advice to match your filesystem to the data block size is
not often given.


>Another potential difference here with SAS vs SATA is the maximum queue
>depth supported by the protocol and drive.

Yes. The interface, and how it is configured, matters greatly.


>SSD drives also do internal housekeeping tasks for wear leveling on writing.

The biggest of which is always writing to a new location.  Enterprise
grade SSD's sometimes do perform erases ahead of time during idle
periods, but cheap drives often wait until the free+dirty space is to
be reused.


>I’ve seen SSD drives benchmark with 80-90MB sequential read or write,
>change the alignment, and you’ll get 400+ on the same drive with sequential
>reads (changing nothing else)
>
>A specific example
>https://www.servethehome.com/ssd-alignment-quickly-benchmark-ssd/

I believe you have seen it, but if the read performance changed that
drastically, then the controller/driver was doing something awfully
stupid ... e.g., re-reading the same data block for each filesystem
block it contains.


YMMV.
George

Extra files in "base" dir not seen in relfilenodes

[Daniel Farina]

I am looking at a database with a wide (~500G) divergence between the total
space expended by the database directory and the result of select
sum(pg_relation_size(oid)) from pg_class;.

I located about 280G of apparent extra space by performing an anti-join
between files on disk and files in the catalog via the pg_class.relfilenode
field.

What should I do to get rid of the data, if it is, in fact, "extra"? Is
there a reasonable bug report to file? Can I independently compute the
entire itemization of files that belong in the data directory?

The cluster was pg_upgrade'd, in link mode, a while ago, to 10.1 from 9.6.
This is not necessarily relevant, though.

Thanks,
Daniel

Re: Extra files in "base" dir not seen in relfilenodes

[Tom Lane]

Daniel Farina  writes:
> I am looking at a database with a wide (~500G) divergence between the total
> space expended by the database directory and the result of select
> sum(pg_relation_size(oid)) from pg_class;.

Odd.

> I located about 280G of apparent extra space by performing an anti-join
> between files on disk and files in the catalog via the pg_class.relfilenode
> field.

Umm ... are you accounting for catalogs that have zeroes in
pg_class.relfilenode?  It's generally better to rely on the
pg_relation_filenode(oid) function than the raw column contents.

regards, tom lane

Re: Extra files in "base" dir not seen in relfilenodes

[Daniel Farina]

On Tue, Jan 16, 2018 at 3:04 PM Tom Lane  wrote:

> Daniel Farina  writes:
> > I am looking at a database with a wide (~500G) divergence between the
> total
> > space expended by the database directory and the result of select
> > sum(pg_relation_size(oid)) from pg_class;.
>
> Odd.
>
> > I located about 280G of apparent extra space by performing an anti-join
> > between files on disk and files in the catalog via the
> pg_class.relfilenode
> > field.
>
> Umm ... are you accounting for catalogs that have zeroes in
> pg_class.relfilenode?  It's generally better to rely on the
> pg_relation_filenode(oid) function than the raw column contents.
>

Yeah, the catalogs are not considered here (oids < 1). The oids in
question are rather high. Let me re-run the antijoin with the function
though

Indeed, same result.

Re: Extra files in "base" dir not seen in relfilenodes

[Tom Lane]

Daniel Farina  writes:
> On Tue, Jan 16, 2018 at 3:04 PM Tom Lane  wrote:
>> Umm ... are you accounting for catalogs that have zeroes in
>> pg_class.relfilenode?  It's generally better to rely on the
>> pg_relation_filenode(oid) function than the raw column contents.

> Yeah, the catalogs are not considered here (oids < 1). The oids in
> question are rather high. Let me re-run the antijoin with the function
> though

> Indeed, same result.

Hmm, you should have gotten a result that was different by the size
of the bootstrap catalogs (pg_class, pg_attribute, pg_proc, pg_type,
plus their indexes).  I'm worried that you're going to accidentally
delete those critical catalogs.

However, assuming you've gotten that detail right, then any file
you can't match up with a relfilenode value must be an orphan you
can just "rm".

Do you have any theories about how the DB got like this?  Were there
system crashes or anything like that recently?  Is there any pattern
to the file access or mod dates on the putatively-orphaned files?

regards, tom lane

Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Michael Paquier]

On Tue, Jan 16, 2018 at 07:05:19PM +0100, Adam Sjøgren wrote:
> This sounds very interesting - we are running PostgreSQL 9.3.20.

Which means that we may be looking at a new bug, 9.3.20 is the latest in
the 9.3 set as of today.

> Did you ever find out exactly what the change that solved the problem
> between 9.4.8 and 9.4.11 was?

In this case, I think that you are looking for this thread:
https://www.postgresql.org/message-id/20160826072658.15676.7...@wrigleys.postgresql.org

And this commit:
commit: a694435641faf26a9a4c210d20576ae836e86c48
author: Tom Lane 
date: Sat, 3 Sep 2016 13:28:53 -0400
Fix corrupt GIN_SEGMENT_ADDITEMS WAL records on big-endian hardware.

Both involved 9.4.8.
--
Michael


signature.asc
Description: PGP signature

Parallel Btree index scan

[Krithika Venkatesh]

Hi All,

Can you please help me to understand what is parallel btree index scan in
Postgres 10.


And what is the difference between index scan and index only scan.

Thanks,
Krithika

Re: ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Adam Sjøgren]

Michael writes:

> On Tue, Jan 16, 2018 at 07:05:19PM +0100, Adam Sjøgren wrote:

>> This sounds very interesting - we are running PostgreSQL 9.3.20.

> Which means that we may be looking at a new bug, 9.3.20 is the latest in
> the 9.3 set as of today.

Yes; unfortunately we have failed to reproduce it outside production.

The fact that the tsvector field is always involved when we see the
error might be of interest (the tsvector field is the most updated in
our database, however).

Just for completeness, the tsvector field has a GIN index on it:

"sequence_fts_idx" gin (fts) WITH (fastupdate=off)

and it is updated by a BEFORE INSERT OR UPDATE trigger.

A new observation is that when we previously were able to get the
"unexpected chunk number" to go away by simply updating the tsvector
field of the offending record, we now have a record where we get "ERROR:
tuple concurrently updated" when we try overwriting the field.

On another record exhibiting the "unexpected chunk number" error, we
could overwrite the fts field, as can we on rows not affected by the
"unexpected chunk number"-error. So it seems the two errors might
somehow be related.

We tried stopping all activity on the database, and still got the
"ERROR: tuple concurrently updated" on the row with "unexpected chunk
number".

Also, the error we are getting is now: "unexpected chunk number 2
(expected 3) for toast value 1498303849 in pg_toast_10919630", where
previously we've only seen "unexpected chunk number 0 (expected 1)".

We are kind of at a loss, so any suggestions on what we could try are
welcome.

>> Did you ever find out exactly what the change that solved the problem
>> between 9.4.8 and 9.4.11 was?

> In this case, I think that you are looking for this thread:
> https://www.postgresql.org/message-id/20160826072658.15676.7...@wrigleys.postgresql.org

> And this commit:
> commit: a694435641faf26a9a4c210d20576ae836e86c48
> author: Tom Lane 
> date: Sat, 3 Sep 2016 13:28:53 -0400
> Fix corrupt GIN_SEGMENT_ADDITEMS WAL records on big-endian hardware.
>
> Both involved 9.4.8.

We run on x86_64-hardware, so I guess this wouldn't affect us?


  Best regards,

Adam

-- 
 "No more than that, but very powerful all theAdam Sjøgren
  same; simple things are good."a...@novozymes.com