Re: Strange runtime partition pruning behaviour with 11.4

2019-08-05 Thread Thomas Kellerer 
Tom Lane schrieb am 03.08.2019 um 18:05:
> Yeah, I get the same plan with or without ANALYZE, too.  In this example,
> having the ANALYZE stats barely moves the rowcount estimates for
> foo_bar_baz at all, so it's not surprising that the plan doesn't change.
> (I do wonder how Thomas got a different outcome...)

I don't know why either ;) 

I am using a JDBC based SQL tool to run that - I don't know if that matters.

I just tried this script with Postgres 12 beta2 and there I do not get 
the initial plan with "never executed" (so the same behaviour as everybody
else seems to have).

If the reason why my initial plan is different than the "analyzed" plan 
lies in the configuration, I am happy to share my postgresql.conf if 
that is of any interest.

Thomas

improving windows functions performance

2019-08-05 Thread Mariel Cherkassky 
Hey,
I have a very big query that consist from 3-4 subqueries that use windows
functions. There is a chance that I'll need to rewrite the query but first
I'm trying to search for other ways to improve it and I'll be happy to hear
if one of u have an idea.

Basically my table has the following structure : (objid,first_num,last_num)
and each record is a range from the first number to the last one for that
specific obj. I'm trying to unite ranges that overlaps. For example :
for the following table :
objid first_num last_num
1  57
1  8 10
2  4   6
2  9   10

I would like to get :
objid first_num last_num
1  510
2  4   6
2  9   10

I have a query that does it but takes about 4s for 1.5M records. I created
an index on (objid,first_num,last_num) in order to use only index scan
instead of seq scan on this table. I wanted to here if u guys have any
other ideas.

Thanks.

Re: improving windows functions performance

2019-08-05 Thread Andreas Kretschmer 




Am 05.08.19 um 22:47 schrieb Mariel Cherkassky:

Hey,
I have a very big query that consist from 3-4 subqueries that use 
windows functions. There is a chance that I'll need to rewrite the 
query but first I'm trying to search for other ways to improve it and 
I'll be happy to hear if one of u have an idea.


Basically my table has the following structure : 
(objid,first_num,last_num) and each record is a range from the first 
number to the last one for that specific obj. I'm trying to unite 
ranges that overlaps. For example :

for the following table :
objid first_num last_num
1          5                7
1          8                 10
2          4                   6
2          9                   10

I would like to get :
objid first_num last_num
1          5                10
2          4                   6
2          9                   10

I have a query that does it but takes about 4s for 1.5M records. I 
created an index on (objid,first_num,last_num) in order to use only 
index scan instead of seq scan on this table. I wanted to here if u 
guys have any other ideas.




you should provide more information, for instance:

* used version
* table-structure
* real query
* execution plan (using explain analyse)

Andreas

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