Re: [GENERAL] Slow query plan used

[Wetzel, Juergen (Juergen)]

Andreas Kretschmer  writes:
> please consider my plan B) and increase the stats. See my other mail.

I tried that also. Combined with the partial index. But still same result.

Bill Moran  writes:
> LIKE queries are probably challenging to plan, especially when they're 
> not
> left-anchored: how can the planner be reasonalbly expected to estimate 
> how many rows will be matched by a given LIKE expression.

That's clear to me. And because of that I expected the planner to use the table 
document as outer table in the nested loop join. Especially as here is an index 
available which gives a restriction to only 130 rows out of the 3.

Tom Lane  writes:
> You might get some traction by creating indexes on lower(searchfield1) etc.  
> This isn't even necessarily with an expectation that the planner would use 
> those indexes in the plan ... but what it would do is make use of the 
> statistics that ANALYZE will accumulate about the indexed expressions.  I 
> think that
> would give you better estimates about the LIKE rowcounts.  You might have to 
> crank up the statistics target for those indexes if the default isn't enough 
> to
> make the estimates significantly better.  (Obviously, don't forget to 
> re-ANALYZE before checking results.)

I will try that. Does that mean the column statistics will only be collected 
when there's an index on the table/column?

Thanks for all your hints. I will go on and try.



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

Re: [GENERAL] jsonb case insensitive search

[Guyren Howe]

On Jun 1, 2017, at 13:44 , Karl Czajkowski  wrote:
> If I remember correctly, isn't a compound index always just using
> btree?  In general, I have found better luck using several smaller
> btree indices than one large compound one.  Unless your entire query
> can be answered from an index-only lookup, the extra columns just
> bloat the btree index.

The issue is *much* more subtle than this. For repetitive queries, a compound 
index can be a great speedup.

Best discussion I’ve seen around index design is on the website 
use-the-index-luke.com.

Re: [GENERAL] repmgr cascade replication node delay

[David G. Johnston]

On Thu, Jun 1, 2017 at 3:30 PM, Juliano  wrote:

> The objective of an extra node 24 hours delayed is to recover some error
> in the server.
> Is it possible to make this configuration?
>

A quick search turns up:​

​​recovery_min_apply_delay

https://www.postgresql.org/docs/current/static/standby-settings.html

No personal experience though.

David J.

[GENERAL] repmgr cascade replication node delay

[Juliano]

Hi guys,

I have a replication scenario master/slave and I would like to create one extra 
more node (cascade from slave) with a time delay of 24-hour replication.

The objective of an extra node 24 hours delayed is to recover some error in the 
server.

Is it possible to make this configuration?

Open to suggestions, please ignore pg_dump and barman.

Regards
Juliano

Re: [GENERAL] dump to pg

[Nicolas Paris]

> If they aren't too big, you might get away by installing the express edition 
> of the respective DBMS, then import them using the native tools, then export 
> the data as CSV files.

Thanks Thomas. Both are binaries. The oracle's one is a 30TB database...


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

Re: [GENERAL] dump to pg

[Thomas Kellerer]

Nicolas Paris schrieb am 31.05.2017 um 16:43:

Hi,

I have dumps from oracle and microsoft sql server (no more details). Is it possible to 
load them "directly" into postgres  (without oracle/mssql license)?
dump -> csv -> postgtres
or something ?


If those are binary dumps (e.g. a DataPump dump in Oracle or a "backup" in SQL 
Server), then the answer is no.
Only Oracle or SQL Server can read those files.

If they aren't too big, you might get away by installing the express edition of 
the respective DBMS, then import them using the native tools, then export the 
data as CSV files.

Thomas




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

Re: [GENERAL] dump to pg

[Nicolas Paris]

Thanks all,

The point is I only have access to dump files, no ora/mssql server instance
access. I have noticed the warning around legality on that question. The
best solution for me is to ask to each. Once get answer, I will come back
here to provide the answer.

2017-06-01 4:14 GMT-04:00 vinny :

> On 2017-05-31 16:43, Nicolas Paris wrote:
>
>> Hi,
>>
>> I have dumps from oracle and microsoft sql server (no more details).
>> Is it possible to load them "directly" into postgres  (without
>> oracle/mssql license)?
>>
>> dump -> csv -> postgtres
>>
>> or something ?
>>
>> Thanks a lot
>>
>
> A very, *very* short trip to google shows that "intelligent converters"
> have tools that can do both:
>
> https://www.convert-in.com/ora2pgs.htm
>
> There is a trial version with limited options, and the full version seems
> to be priced at $49 to $100.
>
> Disclaimer: I have no experience with this company or it's products, I'm
> not affiliated, I just googled
> and copy/pasted the result.
>

Re: [GENERAL] jsonb case insensitive search

[Karl Czajkowski]

On Jun 01, armand pirvu modulated:

> Overall could it be that the optimizer blatantly ignores a scan index which 
> is cheaper than a table scan, or jsonb implementation still has a long way to 
> come up  or the way it is used in my case is not the one designed for ?
> 

If I remember correctly, isn't a compound index always just using
btree?  In general, I have found better luck using several smaller
btree indices than one large compound one.  Unless your entire query
can be answered from an index-only lookup, the extra columns just
bloat the btree index.

So, you might as well use a simpler compound index for the regular
scalar row keys, and this index will be much smaller without the
baggage of the jsonb values at its leaves.  The planner can use the
jsonb from the actual candidate rows if it is going to have to visit
them anyway for other WHERE or SELECT clauses.

If the sparseness of your query is due to the content within the jsonb
values rather than the other scalar row keys, I think you'd need some
kind of GIN index over the contents of the jsonb documents to find the
small subset of candidate rows by these sparse criteria.  Trigram is
just one example of a GIN indexing scheme.

If your jsonb documents are "flat", i.e. just a bag of key value pairs
and not arbitrary nested jsonb structures, you might also explode them
into arrays of keys or values as separate indexed expressions?  Then,
you could GIN index the arrays and quickly find the subset of rows with
certain unusual keys or unusual values, but would still have to follow
up with a more exact check for the combination of key and value.


Karl



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

Re: [GENERAL] jsonb case insensitive search

[armand pirvu]

I apologize before hand replying again on my own reply . I know it is frowned 
upon . My inline comments.

> On Jun 1, 2017, at 2:05 PM, armand pirvu  wrote:
> 
> Thank you Karl and David
> 
> Ideally as far as I can tell the index would need to be show_id, file_id, 
> lower(…)
> 
> 
> The question is if this is  possible ?
> 
> 
> Thanks
> Armand
> 
> 
>> On Jun 1, 2017, at 12:24 PM, Karl Czajkowski  wrote:
>> 
>> On May 31, armand pirvu modulated:
>> 
>>> The idea is that I would like to avoid having an index for each key
>>> possibly wanted to search, but rather say have the whole record then
>>> search by the key and get the key value, thus having one index serving
>>> multiple purposes so to speak
>>> 
>> 
>> First, benchmarking would be important to figure out if any proposed
>> indexing actually speeds up the kinds of queries you want to perform.
>> With the recently added parallel query features, a simpler indexing
>> scheme with some brute-force search might be adequate?
>> 

Not sure what you mean by benchmarking
But I think comparative times , aka 2 seconds vs a couple milliseconds is quite 
a difference. 
A table scan while in certain cases is okay , in a case when there is heavy 
usage on the same part/area , it will become a problem. 


>> But, you could use a search idiom like this:
>> 
>>(lower(json_column::text)::json) -> lower('key') = 'value'::json
>> 
>> This will down-convert the case on all values and keys.  The left-hand
>> parenthetic expression could be precomputed in an expression index to
>> avoid repeated case conversion. But, typical searches will still have
>> to scan the whole index to perform the projection and match the final
>> value tests on the right-hand side.
>> 
>> If you want to do things like substring matching on field values, you
>> might stick with text and using regexp matches:
>> 
>>(lower(json_column::text)) ~ ‘valuepattern'

In this case a regular index will be ignored even though IMO it should scan the 
index and get the needed information
The criteria I am after gets back 9 rows max out of 100k+ records so I say the 
restriction is darn good. Wouldn’t that be the case for the optimizer to pick 
the path with the least resistance aka best restriction ? Granted it uses a 
lower function which and the search in the text column which is the third in 
the index is not really starting form left. But the index starts with show_id , 
file_id and those are always part of the key. I can see though once the 
show_id, file_id is NOT a good restriction anymore , than the last column will 
make the difference . Either case will that not translate into an index scan ? 
Or the index to be considered in this case, event the last column search has to 
follow the left to right, aka not in between search ?  


>> 
>> or more structural searches:
>> 
>>(lower(json_column::text)) ~ '"key": "[^"]*substring[^"]*"'
>> 
>> Here, the left-hand expression could be trigram indexed to help with
>> sparse, substring matching without a full index scan.  We've had good
>> luck using trigram indexing with regexp matching, though I've honestly
>> never used it for the purpose sketched above...
>> 
>> Karl
> 


Seems to me trigram could be the answer since I have some decent results once I 
applied it, more to dig

Overall could it be that the optimizer blatantly ignores a scan index which is 
cheaper than a table scan, or jsonb implementation still has a long way to come 
up  or the way it is used in my case is not the one designed for ?


thanks
Armand






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

Re: [GENERAL] jsonb case insensitive search

[armand pirvu]

Thank you Karl and David

Ideally as far as I can tell the index would need to be show_id, file_id, 
lower(…)


The question is if this is  possible ?


Thanks
Armand


> On Jun 1, 2017, at 12:24 PM, Karl Czajkowski  wrote:
> 
> On May 31, armand pirvu modulated:
> 
>> The idea is that I would like to avoid having an index for each key
>> possibly wanted to search, but rather say have the whole record then
>> search by the key and get the key value, thus having one index serving
>> multiple purposes so to speak
>> 
> 
> First, benchmarking would be important to figure out if any proposed
> indexing actually speeds up the kinds of queries you want to perform.
> With the recently added parallel query features, a simpler indexing
> scheme with some brute-force search might be adequate?
> 
> But, you could use a search idiom like this:
> 
> (lower(json_column::text)::json) -> lower('key') = 'value'::json
> 
> This will down-convert the case on all values and keys.  The left-hand
> parenthetic expression could be precomputed in an expression index to
> avoid repeated case conversion. But, typical searches will still have
> to scan the whole index to perform the projection and match the final
> value tests on the right-hand side.
> 
> If you want to do things like substring matching on field values, you
> might stick with text and using regexp matches:
> 
> (lower(json_column::text)) ~ 'valuepattern'
> 
> or more structural searches:
> 
> (lower(json_column::text)) ~ '"key": "[^"]*substring[^"]*"'
> 
> Here, the left-hand expression could be trigram indexed to help with
> sparse, substring matching without a full index scan.  We've had good
> luck using trigram indexing with regexp matching, though I've honestly
> never used it for the purpose sketched above...
> 
> Karl



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

Re: [GENERAL] Rounding Double Precision or Numeric

[Louis Battuello]

> On Jun 1, 2017, at 12:58 PM, Scott Marlowe  wrote:
> 
> On Thu, Jun 1, 2017 at 10:42 AM, Steve Atkins  > wrote:
>> 
>>> On Jun 1, 2017, at 9:26 AM, Louis Battuello  
>>> wrote:
>>> 
>>> Is the round() function implemented differently for double precision than 
>>> for numeric? Forgive me if this exists somewhere in the documentation, but 
>>> I can't seem to find it.
>> 
>> https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
>> 
>> "When rounding values, the numeric type rounds ties away from zero, while 
>> (on most machines) the real and double precision types round ties to the 
>> nearest even number.".
>> 
>>> Why does the algorithm vary by data type?
>> 
>> Just guessing, but I'd assume because the NUMERIC type behaves as required 
>> by the SQL spec, while float and double are vanilla IEEE754 arithmetic and 
>> will do whatever the underlying hardware is configured to do, usually round 
>> to nearest even.
>> 
> 
> Exactly. It's important to remember that floats and doubles are
> imprecise representations meant for speed, while numeric is designed
> for accuracy not speed.
> 
> If one needs an exact answer, one does not use floats.
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org 
> )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general 
> 

Perfectly clear now. Thank you!

Re: [GENERAL] jsonb case insensitive search

[Karl Czajkowski]

On May 31, armand pirvu modulated:

> The idea is that I would like to avoid having an index for each key
> possibly wanted to search, but rather say have the whole record then
> search by the key and get the key value, thus having one index serving
> multiple purposes so to speak
> 

First, benchmarking would be important to figure out if any proposed
indexing actually speeds up the kinds of queries you want to perform.
With the recently added parallel query features, a simpler indexing
scheme with some brute-force search might be adequate?

But, you could use a search idiom like this:

 (lower(json_column::text)::json) -> lower('key') = 'value'::json

This will down-convert the case on all values and keys.  The left-hand
parenthetic expression could be precomputed in an expression index to
avoid repeated case conversion. But, typical searches will still have
to scan the whole index to perform the projection and match the final
value tests on the right-hand side.

If you want to do things like substring matching on field values, you
might stick with text and using regexp matches:

 (lower(json_column::text)) ~ 'valuepattern'

or more structural searches:

 (lower(json_column::text)) ~ '"key": "[^"]*substring[^"]*"'

Here, the left-hand expression could be trigram indexed to help with
sparse, substring matching without a full index scan.  We've had good
luck using trigram indexing with regexp matching, though I've honestly
never used it for the purpose sketched above...

Karl


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

Re: [GENERAL] Rounding Double Precision or Numeric

[Scott Marlowe]

On Thu, Jun 1, 2017 at 10:42 AM, Steve Atkins  wrote:
>
>> On Jun 1, 2017, at 9:26 AM, Louis Battuello  
>> wrote:
>>
>> Is the round() function implemented differently for double precision than 
>> for numeric? Forgive me if this exists somewhere in the documentation, but I 
>> can't seem to find it.
>
> https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL
>
> "When rounding values, the numeric type rounds ties away from zero, while (on 
> most machines) the real and double precision types round ties to the nearest 
> even number.".
>
>> Why does the algorithm vary by data type?
>
> Just guessing, but I'd assume because the NUMERIC type behaves as required by 
> the SQL spec, while float and double are vanilla IEEE754 arithmetic and will 
> do whatever the underlying hardware is configured to do, usually round to 
> nearest even.
>

Exactly. It's important to remember that floats and doubles are
imprecise representations meant for speed, while numeric is designed
for accuracy not speed.

If one needs an exact answer, one does not use floats.


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

Re: [GENERAL] Rounding Double Precision or Numeric

[Steve Atkins]

> On Jun 1, 2017, at 9:26 AM, Louis Battuello  
> wrote:
> 
> Is the round() function implemented differently for double precision than for 
> numeric? Forgive me if this exists somewhere in the documentation, but I 
> can't seem to find it.

https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

"When rounding values, the numeric type rounds ties away from zero, while (on 
most machines) the real and double precision types round ties to the nearest 
even number.".

> Why does the algorithm vary by data type?

Just guessing, but I'd assume because the NUMERIC type behaves as required by 
the SQL spec, while float and double are vanilla IEEE754 arithmetic and will do 
whatever the underlying hardware is configured to do, usually round to nearest 
even.

Cheers,
  Steve



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

Re: [GENERAL] Rounding Double Precision or Numeric

[Torsten Förtsch]

This is documented in section 8.1.2 in the manual. (
https://www.postgresql.org/docs/9.6/static/datatype-numeric.html)

NUMERIC rounds away from zero.

IEEE 754 based data types (FLOAT, DOUBLE PRECISION) round to the closest
even number.

On Thu, Jun 1, 2017 at 6:26 PM, Louis Battuello  wrote:

> Is the round() function implemented differently for double precision than
> for numeric? Forgive me if this exists somewhere in the documentation, but
> I can't seem to find it.
>
> I've noticed with 9.6 on OSX, the .5 rounding is handled differently
> between the types. (I haven't tested other versions, yet.) For double
> precision values, even whole numbers are rounded down, yet for odds they
> are rounded up. For numeric values, all .5 numbers are rounded up.
>
> psql (9.6.3)
> Type "help" for help.
>
> postgres=# \x
> Expanded display is on.
> postgres=# select round(cast(1230.5 as double precision)) as
> round_double_even_0
> postgres-#   ,round(cast(1231.5 as double precision)) as
> round_double_odd_1
> postgres-#   ,round(cast(1232.5 as double precision)) as
> round_double_even_2
> postgres-#   ,round(cast(1233.5 as double precision)) as
> round_double_odd_3
> postgres-#   ,round(cast(1234.5 as double precision)) as
> round_double_even_4
> postgres-#   ,round(cast(1235.5 as double precision)) as
> round_double_odd_5
> postgres-# ;
>
> -[ RECORD 1 ]---+-
> round_double_even_0 | 1230
> round_double_odd_1  | 1232
> round_double_even_2 | 1232
> round_double_odd_3  | 1234
> round_double_even_4 | 1234
> round_double_odd_5  | 1236
>
> postgres=# select round(cast(1230.5 as numeric)) as round_numeric_even_0
> postgres-#   ,round(cast(1231.5 as numeric)) as round_numeric_odd_1
> postgres-#   ,round(cast(1232.5 as numeric)) as round_numeric_even_2
> postgres-#   ,round(cast(1233.5 as numeric)) as round_numeric_odd_3
> postgres-#   ,round(cast(1234.5 as numeric)) as round_numeric_even_4
> postgres-#   ,round(cast(1235.5 as numeric)) as round_numeric_odd_5
> postgres-# ;
>
> -[ RECORD 1 ]+-
> round_numeric_even_0 | 1231
> round_numeric_odd_1  | 1232
> round_numeric_even_2 | 1233
> round_numeric_odd_3  | 1234
> round_numeric_even_4 | 1235
> round_numeric_odd_5  | 1236
>
> postgres=# select round(1230.5) as round_even_0
>  ,round(1231.5) as round_odd_1
>  ,round(1232.5) as round_even_2
>  ,round(1233.5) as round_odd_3
>  ,round(1234.5) as round_even_4
>  ,round(1235.5) as round_odd_5
> ;
>
> -[ RECORD 1 ]+-
> round_even_0 | 1231
> round_odd_1  | 1232
> round_even_2 | 1233
> round_odd_3  | 1234
> round_even_4 | 1235
> round_odd_5  | 1236
>
> postgres=# \q
>
> Why does the algorithm vary by data type?
>
> Or is something entirely different happening?
>
>

Re: [GENERAL] Slow query plan used

[Tom Lane]

Bill Moran  writes:
> LIKE queries are probably challenging to plan, especially when they're not
> left-anchored: how can the planner be reasonalbly expected to estimate how
> many rows will be matched by a given LIKE expression.

Yeah, especially without any statistics.  The core problem here appears
to be the poor rowcount estimate for the LIKE:

  ->  Seq Scan on c3k_document_index  (cost=0.00..15160.48 
rows=1063 width=285) (actual time=0.206..13539.353 rows=33022 loops=1)
Filter: ((lower((searchfield1)::text) ~~ '%sehr%'::text) OR 
(lower(searchfield8) ~~ '%sehr%'::text))
Rows Removed by Filter: 33832

That's off by a factor of 30.

> Not having looked at the code, I would guess that the length of the LIKE
> expression will make the planner assume that the match is more restrictive,
> while many % and _ in the LIKE expression make the planner assume that the
> match is less restrictive.

Exactly.  It's not terribly bright, but the extra %'s make it think that
the pattern is less selective, producing a higher rowcount estimate that
--- by chance -- better matches reality.  Then you get better decisions
about how to shape the rest of the plan.

You might get some traction by creating indexes on lower(searchfield1)
etc.  This isn't even necessarily with an expectation that the planner
would use those indexes in the plan ... but what it would do is make
use of the statistics that ANALYZE will accumulate about the indexed
expressions.  I think that would give you better estimates about the
LIKE rowcounts.  You might have to crank up the statistics target for
those indexes if the default isn't enough to make the estimates
significantly better.  (Obviously, don't forget to re-ANALYZE before
checking results.)

> That in mind, let me throw pg_trgm into the mix of things to try:
> https://www.postgresql.org/docs/current/static/pgtrgm.html
> The trigram module allows you to create indexes that LIKE can use
> to do index searches instead of always having to do sequential scans
> or push the LIKE matching to another part of the plan tree.

For cases like the above, where half the table is getting selected,
I would not expect the planner to use an index anyway.  Any old index
on lower(searchfieldN) will work in terms of inducing ANALYZE to
collect stats.  But if you have other queries with more selective
LIKE patterns then maybe a trigram index in particular is worthwhile.

regards, tom lane


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

[GENERAL] Rounding Double Precision or Numeric

[Louis Battuello]

Is the round() function implemented differently for double precision than for 
numeric? Forgive me if this exists somewhere in the documentation, but I can't 
seem to find it.

I've noticed with 9.6 on OSX, the .5 rounding is handled differently between 
the types. (I haven't tested other versions, yet.) For double precision values, 
even whole numbers are rounded down, yet for odds they are rounded up. For 
numeric values, all .5 numbers are rounded up.

psql (9.6.3)
Type "help" for help.

postgres=# \x
Expanded display is on.
postgres=# select round(cast(1230.5 as double precision)) as round_double_even_0
postgres-#   ,round(cast(1231.5 as double precision)) as round_double_odd_1
postgres-#   ,round(cast(1232.5 as double precision)) as round_double_even_2
postgres-#   ,round(cast(1233.5 as double precision)) as round_double_odd_3
postgres-#   ,round(cast(1234.5 as double precision)) as round_double_even_4
postgres-#   ,round(cast(1235.5 as double precision)) as round_double_odd_5
postgres-# ;

-[ RECORD 1 ]---+-
round_double_even_0 | 1230
round_double_odd_1  | 1232
round_double_even_2 | 1232
round_double_odd_3  | 1234
round_double_even_4 | 1234
round_double_odd_5  | 1236

postgres=# select round(cast(1230.5 as numeric)) as round_numeric_even_0
postgres-#   ,round(cast(1231.5 as numeric)) as round_numeric_odd_1
postgres-#   ,round(cast(1232.5 as numeric)) as round_numeric_even_2
postgres-#   ,round(cast(1233.5 as numeric)) as round_numeric_odd_3
postgres-#   ,round(cast(1234.5 as numeric)) as round_numeric_even_4
postgres-#   ,round(cast(1235.5 as numeric)) as round_numeric_odd_5
postgres-# ;

-[ RECORD 1 ]+-
round_numeric_even_0 | 1231
round_numeric_odd_1  | 1232
round_numeric_even_2 | 1233
round_numeric_odd_3  | 1234
round_numeric_even_4 | 1235
round_numeric_odd_5  | 1236

postgres=# select round(1230.5) as round_even_0
 ,round(1231.5) as round_odd_1
 ,round(1232.5) as round_even_2
 ,round(1233.5) as round_odd_3
 ,round(1234.5) as round_even_4
 ,round(1235.5) as round_odd_5
;

-[ RECORD 1 ]+-
round_even_0 | 1231
round_odd_1  | 1232
round_even_2 | 1233
round_odd_3  | 1234
round_even_4 | 1235
round_odd_5  | 1236

postgres=# \q

Why does the algorithm vary by data type?

Or is something entirely different happening?

Re: [GENERAL] Slow query plan used

[Bill Moran]

On Thu, 1 Jun 2017 16:45:17 +0200
Andreas Kretschmer  wrote:
> 
> Am 01.06.2017 um 14:07 schrieb Wetzel, Juergen (Juergen):
> >
> > Am 31.05.2017 um 13:27 schrieb Wetzel, Juergen (Juergen):
>  Only 130 rows out of the 3 have ARCHIVED = 0
> >>> in this case i would suggest a partial index:
> >>> create index  on  (archived) where archived = 0;
> >> Thanks, Andreas.
> >>
> >> Sorry for the confusion about the table names.
> >> The hint with the partial index sounds as it could solve the problem. I 
> >> will test it.
> >>
> > Hi,
> >
> > I created now a partial index
> > create index on document (archived) where archived = '0';
> 
> just to be sure: this syntay is wrong, missing index-name. But it seems 
> the index is document_archived_idx ...
> 
> > But result is same as before: a short like expression included in doubled 
> > %-signs leads to a fast query plan whereas a longer like expression or use 
> > of single %-sign creates a much slower query. Please see below query plans. 
> > Most surprisingly to me is the influence of the like expression, especially 
> > the doubled %-sign on short expressions. Any other ideas how to speed up 
> > that query or what is going on here in general?

LIKE queries are probably challenging to plan, especially when they're not
left-anchored: how can the planner be reasonalbly expected to estimate how
many rows will be matched by a given LIKE expression.

Not having looked at the code, I would guess that the length of the LIKE
expression will make the planner assume that the match is more restrictive,
while many % and _ in the LIKE expression make the planner assume that the
match is less restrictive. Extrapolate that into guessing a number of matched
tuples and how that fits into the overall plan and you'll probaby give
yourself a brain anuerism. While having a detailed understanding of exactly
how the planner makes such decisions is certainly worthwhile, I would
recommend a more pragmatic approach: try things and see what works.

That in mind, let me throw pg_trgm into the mix of things to try:
https://www.postgresql.org/docs/current/static/pgtrgm.html
The trigram module allows you to create indexes that LIKE can use
to do index searches instead of always having to do sequential scans
or push the LIKE matching to another part of the plan tree. Based on
your described situation, I have a theory that it might improve things
quite a bit.

-- 
Bill Moran 


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

Re: [GENERAL] Slow query plan used

[Andreas Kretschmer]

Am 01.06.2017 um 14:07 schrieb Wetzel, Juergen (Juergen):


Am 31.05.2017 um 13:27 schrieb Wetzel, Juergen (Juergen):

Only 130 rows out of the 3 have ARCHIVED = 0

in this case i would suggest a partial index:
create index  on  (archived) where archived = 0;

Thanks, Andreas.

Sorry for the confusion about the table names.
The hint with the partial index sounds as it could solve the problem. I will 
test it.


Hi,

I created now a partial index
create index on document (archived) where archived = '0';


just to be sure: this syntay is wrong, missing index-name. But it seems 
the index is document_archived_idx ...





But result is same as before: a short like expression included in doubled 
%-signs leads to a fast query plan whereas a longer like expression or use of 
single %-sign creates a much slower query. Please see below query plans. Most 
surprisingly to me is the influence of the like expression, especially the 
doubled %-sign on short expressions. Any other ideas how to speed up that query 
or what is going on here in general?



please consider my plan B) and increase the stats. See my other mail.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com



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

Re: [GENERAL] Redo the filenode link in tablespace

[Adrian Klaver]

On 06/01/2017 03:47 AM, tel medola wrote:

Did you get any help with this?
/I formatted correctly before sending the email. Maybe you should ask 
yourself if the mail server did not remove the formatting./


I was talking about help with your relfilenode issue, I learned to deal 
with the formatting awhile ago.



/
/
Well the relpages, reltuples are estimated values that can be updated 
with an ANALYZE./

/

/I can not make analyze on a table whose filenode is pointing to another 
reference. The table is empty, just because the filenode does not point 
to the correct ID./


Understood, I was just saying that if you could get the table pointing 
at the right relfilenode the other values would be synced up with an 
ANALYZE.


At this point it is time to just try something. You have file level 
backups of the various backups, so you can restore that if something 
goes wrong, correct?


For 01052016.repositorio with current pg_class entry of relfilenode of 
13741352, change that back to the old entry of 5214489.






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


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

Re: [GENERAL] Slow query plan used

[Wetzel, Juergen (Juergen)]

Am 31.05.2017 um 13:27 schrieb Wetzel, Juergen (Juergen):
>>> Only 130 rows out of the 3 have ARCHIVED = 0
>> in this case i would suggest a partial index:
>> create index  on  (archived) where archived = 0;
> Thanks, Andreas.
>
> Sorry for the confusion about the table names.
> The hint with the partial index sounds as it could solve the problem. I will 
> test it.
>

Hi,

I created now a partial index
create index on document (archived) where archived = '0'; 

But result is same as before: a short like expression included in doubled 
%-signs leads to a fast query plan whereas a longer like expression or use of 
single %-sign creates a much slower query. Please see below query plans. Most 
surprisingly to me is the influence of the like expression, especially the 
doubled %-sign on short expressions. Any other ideas how to speed up that query 
or what is going on here in general?

Thanks,
Jürgen

Limit  (cost=24327.12..24327.21 rows=38 width=662) (actual 
time=15373.542..15373.565 rows=136 loops=1)
  Buffers: shared hit=264747 read=51242 dirtied=6 written=16
  ->  Sort  (cost=24327.12..24327.21 rows=38 width=662) (actual 
time=15373.541..15373.557 rows=136 loops=1)
Sort Key: document.created, document.id
Sort Method: quicksort  Memory: 102kB
Buffers: shared hit=264747 read=51242 dirtied=6 written=16
->  Nested Loop  (cost=0.42..24326.12 rows=38 width=662) (actual 
time=10.951..15372.914 rows=136 loops=1)
  Buffers: shared hit=264741 read=51242 dirtied=6 written=16
  ->  Seq Scan on document_index  (cost=0.00..15403.68 rows=1257 
width=289) (actual time=0.205..14901.743 rows=38545 loops=1)
Filter: ((lower((searchfield1)::text) ~~ '%sehr%'::text) OR 
(lower(searchfield8) ~~ '%sehr%'::text))
Rows Removed by Filter: 40531
Buffers: shared hit=123181 read=38272 dirtied=5 written=12
  ->  Index Scan using document_6720023941 on document  
(cost=0.42..7.09 rows=1 width=381) (actual time=0.011..0.011 rows=0 loops=38545)
Index Cond: (id = document_index.documentid)
Filter: (((folder)::text = ANY 
('{*INBOX,*DELAYED}'::text[])) AND (archived = '0'::bpchar) AND 
((umr_actualtopicid)::text = ANY 
('{f3fb34574100,804840564100,4fc81b554100,d27d9c4d41000200,e9aba54d4100,4aaf905441000a00,737
 (...)
Rows Removed by Filter: 1
Buffers: shared hit=141560 read=12970 dirtied=1 written=4
Total runtime: 15373.763 ms


Limit  (cost=34194.37..34194.74 rows=150 width=662) (actual 
time=181.502..181.525 rows=134 loops=1)
  Buffers: shared hit=8022 read=277
  ->  Sort  (cost=34194.37..34194.83 rows=186 width=662) (actual 
time=181.501..181.516 rows=134 loops=1)
Sort Key: document.created, c3k_document.id
Sort Method: quicksort  Memory: 101kB
Buffers: shared hit=8022 read=277
->  Nested Loop  (cost=3546.02..34187.36 rows=186 width=662) (actual 
time=32.660..181.064 rows=134 loops=1)
  Buffers: shared hit=8022 read=277
  ->  Bitmap Heap Scan on document  (cost=3545.61..19272.79 
rows=2375 width=381) (actual time=22.771..96.683 rows=458 loops=1)
Recheck Cond: ((archived = '0'::bpchar) AND (ownerid = ANY 
('{5000239,5000238,5000234,5000113,5000237,5000236,5000230,5000112,5000233,5000111,5000232,13,15,16,18,19,5000249,5000246,5000124,5000245,5000127,5000247,5000242,5000120,5000123
 (...)
Rows Removed by Index Recheck: 15733
Filter: ((actualtopicid)::text = ANY 
('{f3fb34574100,804840564100,4fc81b554100,d27d9c4d41000200,e9aba54d4100,4aaf905441000a00,737e9c4d41000900,3ecdec4d4100,4aaf905441000800,4aaf905441000e00,fc7e9c4d41000f00,11ffc
 (...)
Rows Removed by Filter: 27
Buffers: shared hit=5677
->  BitmapAnd  (cost=3545.61..3545.61 rows=6228 width=0) 
(actual time=22.056..22.056 rows=0 loops=1)
  Buffers: shared hit=2470
  ->  Bitmap Index Scan on document_archived_idx  
(cost=0.00..1131.17 rows=54784 width=0) (actual time=11.694..11.694 rows=60295 
loops=1)
Index Cond: (archived = '0'::bpchar)
Buffers: shared hit=184
  ->  Bitmap Index Scan on 
document_ownerid_folder_status_idx  (cost=0.00..2413.00 rows=8973 width=0) 
(actual time=8.718..8.718 rows=14962 loops=1)
Index Cond: ((ownerid = ANY 
('{5000239,5000238,5000234,5000113,5000237,5000236,5000230,5000112,5000233,5000111,5000232,13,15,16,18,19,5000249,5000246,5000124,5000245,5000127,5000247,5000242,5000120,5000123,5000244,5000122,50
 (...)
Buffers: shared hit=2286
  ->  Index Scan using document_7965268402 on document_index  
(cost=0.42..6.27 rows=1 

Re: [GENERAL] Redo the filenode link in tablespace

[tel medola]

Did you get any help with this?
*I formatted correctly before sending the email. Maybe you should ask
yourself if the mail server did not remove the formatting.*

Well the relpages, reltuples are estimated values that can be updated with
an ANALYZE.

*I can not make analyze on a table whose filenode is pointing to another
reference. The table is empty, just because the filenode does not point to
the correct ID.*

2017-05-31 20:22 GMT-03:00 Adrian Klaver :

> On 05/31/2017 06:05 AM, tel medola wrote:
>
>> Hi.
>> I have a rather serious problem in my database. I'll try to summarize
>> what happened and how far I've gotten with the help of friends from the
>> pgsql-sql list.
>>
>>
>
>> When I returned the copy of the drives, the records were no longer found.
>> For example, if I make a "select count (*) from" 01052016 ".repository",
>> the record amount will result to 0. But all the binaries are there, intact.
>>
>> As I said above, with the help of friends from the pgsql-sql list, I
>> managed to find the problem. When I did the truncate, the data was erased
>> and the filenode was recreated and pointed to a zero file. Doing this query:
>> select pg_relation_filenode ('01052016.repository' :: regclass), it
>> returns me: 13741352, when the correct link (before truncate) was 5214489.
>>
>> Now, doing this other query:
>> select c. *
>>  From pg_class c
>> Where c.relfilenode = 13741352
>> He returns me:
>>
>> relnamerelnamespacereltypereloftyperelownerrelam*relfilenode
>> *reltablespacerelpagesreltuplesrelallvisiblereltoastrelidrel
>> toastidxidrelhasindexrelissharedrelpersistencerelkindrelnatt
>> srelchecksrelhasoidsrelhaspkeyrelhasrulesrelhastriggersrelha
>> ssubclassrelispopulatedrelfrozenxidrelminmxidrelaclreloptions
>> repositorio520596252144910100*13741352*52059152144930Tru
>> eFalsepr70FalseTrueFalseFalseFalseTrue93602881
>>
>> Now the question I need the help of friends ...
>>
>> Is it possible to re-link to the item before truncate?
>>
>
> Did you get any help with this?
>
> I noticed that it is not only a change of the field relfilenode, because
>> there are more fields to be informed, such as (relpages, reltuples).
>>
>
> Well the relpages, reltuples are estimated values that can be updated with
> an ANALYZE.
>
>
>
>> Has anyone ever had to do this and succeeded?
>>
>>
>> Every help is welcome. I have a part of the production stopped and some
>> jobs are at risk.
>>
>> Thanks
>> Roberto.
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Build PostgreSQL With XML Support on Linux

[Osahon Oduware]

Hi Tom,

Thanks a lot for your response. You made my day! Your thought in the 2nd
paragraph of your response (the "make distclean" stuff) was the solution.
It was an oversight on my part. Thanks once again.

On Wed, May 31, 2017 at 4:49 PM, Tom Lane  wrote:

> Osahon Oduware  writes:
> > Thanks for the response. Yes, I did all of that.
>
> Well, better start double-checking, because it certainly appears that
> the version of pg_config you're calling is not from a --with-libxml
> build.
>
> After thinking a bit, another possible way of messing this up would be
> if you were working in a previously-used build tree, and simply did
> a new configure run and then "make" without cleaning up the old build
> products.  You might be able to get away with that if you'd used
> --enable-depend, but without it you really need at least "make clean"
> and preferably "make distclean" before reconfiguring.
>
> regards, tom lane
>

Re: [GENERAL] dump to pg

[vinny]

On 2017-05-31 16:43, Nicolas Paris wrote:

Hi,

I have dumps from oracle and microsoft sql server (no more details).
Is it possible to load them "directly" into postgres  (without
oracle/mssql license)?

dump -> csv -> postgtres

or something ?

Thanks a lot


A very, *very* short trip to google shows that "intelligent converters" 
have tools that can do both:


https://www.convert-in.com/ora2pgs.htm

There is a trial version with limited options, and the full version 
seems to be priced at $49 to $100.


Disclaimer: I have no experience with this company or it's products, I'm 
not affiliated, I just googled

and copy/pasted the result.


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