RE: Get COUNT results from two different columns

[Clive Swan]

Hi David,

 

Thanks for the advice much appreciated.

It is the first time that I have encountered this requirement, so was a bit 
lost in the forest..

 

As I only want a single id and value (new – old), would it be more efficient to 
use a SUBTRACT rather than a UNION functions??

 

The UNION is working now… thanks..

 

 

-- UNION 

SELECT union_subquery.new_sup_id, sum(new_count)

 

FROM

(

SELECT 

new_sup AS new_sup_id, COUNT(new_sup) AS new_count

FROM public."data" 

GROUP BY new_sup_id

 

UNION

 

SELECT old_sup, 

- COUNT(old_sup) AS old_count

FROM public."data"

GROUP BY old_sup

)

AS union_subquery (new_sup_id, new_count)

GROUP BY union_subquery.new_sup_id, union_subquery.new_count

 

 

 

From: David G. Johnston [mailto:david.g.johns...@gmail.com] 
Sent: 23 September 2021 17:45
To: clives...@gmail.com
Cc: pgsql-generallists.postgresql.org
Subject: Re: Get COUNT results from two different columns

 

On Thu, Sep 23, 2021 at 6:37 AM Clive Swan  wrote:

Greetings,

I have two separate queries that work individually, returning a count from each 
column.

 

I want to subtract New(COUNT) from Old(Count)

 

I get an error when trying to run UNION?

 

While you finally did provide this info, it is important to be upfront about 
exactly what error you are receiving.

 

Honestly, it feels odd leveraging UNION to solve this problem, but upon further 
reflection it does provide a nice solution.

 

/* an (incomplete) subquery to be substituted into the main query below /*

SELECT id, count(*) AS positive_counts --positive values

UNION ALL

SELECT id, (- count(*)) AS negative_counts --negative of the count

 

You now have a table where IDs (can) repeat, but at most appear only twice, 
once with a positive count and once with a negative count.  All you need to do 
to get your final answer is sum the positive and negative count together for 
each ID.

 

SELECT union_subquery.id, sum(union_subquery.counted)

 

FROM (/*this is a subquery, in the FROM clause */) AS union_subquery (id, 
counted)

 

 -- which requires an alias (name), which your query omitted and why you got 
the error you described.

 

-- I added column aliases here to emphasize that there are only two output 
columns

-- the name of the second column is originally taken from the first unioned 
query

-- (so, positive_counts, the name negative_counts is discarded once the union 
is complete.

-- But since writing sum(positive_counts) in the main query would be confusing 
I renamed

-- the column to just "counted" using the alias clause

 

GROUP BY union_subquery.id

 

David J.

 

Re: Faster distinct query?

[Israel Brewster]

> On Sep 23, 2021, at 8:55 PM, Michael Lewis  wrote:
> 
> It’s only when I add in the AND data.channels=channels.channel that the query 
> time blows up to 5+ minutes. I personally don’t understand why there would be 
> such a large difference between the two queries - something wrong with my 
> indexes?
> 
> Did you do the manual vacuum as suggested by Tom?

I ran a VACUUM ANALYZE, yes.

> Maybe at least check pg_stat_user_tables for the last vacuum or autovacuum 
> timestamp, and how many records have changed since then.

volcano_seismology=# SELECT 
seq_scan,seq_tup_read,idx_scan,idx_tup_fetch,n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup,n_mod_since_analyze,n_ins_since_vacuum,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze,vacuum_count,autovacuum_count,analyze_count,autoanalyze_count
 FROM pg_stat_user_tables WHERE relname='data';
-[ RECORD 1 ]---+--
seq_scan| 95
seq_tup_read| 25899340540
idx_scan| 728372
idx_tup_fetch   | 51600217033
n_tup_ins   | 840283699
n_tup_upd   | 66120702
n_tup_del   | 2375651
n_tup_hot_upd   | 0
n_live_tup  | 839266956
n_dead_tup  | 66585751
n_mod_since_analyze | 58896052
n_ins_since_vacuum  | 24890460
last_vacuum | 2021-09-22 21:32:11.367855+00
last_autovacuum | 2021-09-14 07:13:23.745862+00
last_analyze| 2021-09-22 21:32:21.071092+00
last_autoanalyze| 2021-09-21 11:54:36.924762+00
vacuum_count| 1
autovacuum_count| 1
analyze_count   | 1
autoanalyze_count   | 2

Note that the update count was due to a (hopefully) one-time process where I 
had to change the value of a bunch of records. Generally this *should be* an 
insert-once-read-many database.


> The system is deciding on an index scan because that should be very fast, but 
> if it needs to look at the actual table table to determine if the process 
> executing that query should still be allowed to see that tuple (row version) 
> then the index only scan slows down a lot I believe. The huge number of heap 
> fetches that Tom pointed out mean that the system doesn't know that ALL 
> processes should be able to see those tuples. As someone else suggested 
> lowering the max freeze age, you might just do a manual "vacuum freeze" as 
> well to allow future auto vacuum processes to quickly skip over those pages 
> as well.

Ok, ran a VACUUM (ANALYZE, FREEZE), and am now showing this from 
pg_stat_user_tables:

volcano_seismology=# SELECT 
seq_scan,seq_tup_read,idx_scan,idx_tup_fetch,n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup,n_mod_since_analyze,n_ins_since_vacuum,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze,vacuum_count,autovacuum_count,analyze_count,autoanalyze_count
 FROM pg_stat_user_tables WHERE relname='data';
-[ RECORD 1 ]---+--
seq_scan| 96
seq_tup_read| 26737263238
idx_scan| 732396
idx_tup_fetch   | 52571927369
n_tup_ins   | 841017819
n_tup_upd   | 66120702
n_tup_del   | 2388723
n_tup_hot_upd   | 0
n_live_tup  | 840198830
n_dead_tup  | 10173
n_mod_since_analyze | 0
n_ins_since_vacuum  | 0
last_vacuum | 2021-09-24 17:18:18.34282+00
last_autovacuum | 2021-09-14 07:13:23.745862+00
last_analyze| 2021-09-24 17:18:31.576238+00
last_autoanalyze| 2021-09-21 11:54:36.924762+00
vacuum_count| 2
autovacuum_count| 1
analyze_count   | 2
autoanalyze_count   | 2

However, adding the AND data.channels=channels.channel to the query still makes 
it take around 5 minutes (https://explain.depesz.com/s/7hb1 
). So, again, running VACUUM didn’t appear 
to help any.

Also perhaps interestingly, if I again modify the query to only match on 
channel, not station, it is again fast (though not quite as fast): 
https://explain.depesz.com/s/HLb8 

So, basically, I can quickly get a list of all channels for which I have data, 
or all stations for which I have data, but getting a list of all channels for 
each station is slow.

> I've heard of the loose indexscan before mentioned on this thread, but I'm 
> not seeing how to implement that for multiple columns. Anyone have an example 
> or pseudo code perhaps?

This is my stumbling block to implementing this option as well. That said, with 
the lookup table in place, perhaps all the rest of this is a moot point? 

---
Israel Brewster
Software Engineer
Alaska Volcano Observatory 
Geophysical Institute - UAF 
2156 Koyukuk Drive 
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145

Using XMLNAMESPACES with XMLEMENT

[Garfield Lewis]

Hi All,

I am attempting to port the following statement from DB2z to Postgres:


SELECT e.empno, e.firstnme, e.lastname,

  XMLELEMENT ( NAME "foo:Emp",

XMLNAMESPACES('http://www.foo.com' AS "foo"),

XMLATTRIBUTES(e.empno as "serial"),

  e.firstnme,

  e.lastname ) AS "Result"

   FROM EMP e

   WHERE e.edlevel = 12;

The NAMESPACES function is not supported by Postgres in the XMLELEMENT 
function. Is there any way to get this to work? I’ve looked at the WITH syntax 
but it doesn’t look like that will be helpful here.

Regards,
Garfield

Re: Using XMLNAMESPACES with XMLEMENT

[Rob Sargent]

> On Sep 24, 2021, at 3:44 PM, Garfield Lewis  wrote:
> 
> 
> Hi All,
>  
> I am attempting to port the following statement from DB2z to Postgres:
>  
> SELECT e.empno, e.firstnme, e.lastname,
>   XMLELEMENT ( NAME "foo:Emp",
> XMLNAMESPACES('http://www.foo.com' AS "foo"),
> XMLATTRIBUTES(e.empno as "serial"),
>   e.firstnme,
>   e.lastname ) AS "Result"
>FROM EMP e
>WHERE e.edlevel = 12;
>  
> The NAMESPACES function is not supported by Postgres in the XMLELEMENT 
> function. Is there any way to get this to work? I’ve looked at the WITH 
> syntax but it doesn’t look like that will be helpful here.
>  

Which Postgres version?  Have you tried path function?
> Regards,
> Garfield

Re: Using XMLNAMESPACES with XMLEMENT

[Garfield Lewis]

I’m using PG v12.6 and no I haven’t tried the path function. Do you have an 
example?

--
Regards,
Garfield A. Lewis

From: Rob Sargent 
Date: Friday, September 24, 2021 at 6:52 PM
To: Garfield Lewis 
Cc: "pgsql-gene...@postgresql.org" 
Subject: Re: Using XMLNAMESPACES with XMLEMENT




On Sep 24, 2021, at 3:44 PM, Garfield Lewis  wrote:
Hi All,

I am attempting to port the following statement from DB2z to Postgres:


SELECT e.empno, e.firstnme, e.lastname,

  XMLELEMENT ( NAME "foo:Emp",

XMLNAMESPACES('http://www.foo.com' AS "foo"),

XMLATTRIBUTES(e.empno as "serial"),

  e.firstnme,

  e.lastname ) AS "Result"

   FROM EMP e

   WHERE e.edlevel = 12;

The NAMESPACES function is not supported by Postgres in the XMLELEMENT 
function. Is there any way to get this to work? I’ve looked at the WITH syntax 
but it doesn’t look like that will be helpful here.


Which Postgres version?  Have you tried path function?
Regards,
Garfield

Re: Using XMLNAMESPACES with XMLEMENT

[Rob Sargent]

> On Sep 24, 2021, at 4:54 PM, Garfield Lewis  wrote:
> 
> 
> I’m using PG v12.6 and no I haven’t tried the path function. Do you have an 
> example?
>  
> -- 
> Regards,
> Garfield A. Lewis
>  
> From: Rob Sargent 
> Date: Friday, September 24, 2021 at 6:52 PM
> To: Garfield Lewis 
> Cc: "pgsql-gene...@postgresql.org" 
> Subject: Re: Using XMLNAMESPACES with XMLEMENT
>  
>  
> 
> 
> On Sep 24, 2021, at 3:44 PM, Garfield Lewis  wrote:
> 
> Hi All,
>  
> I am attempting to port the following statement from DB2z to Postgres:
>  
> SELECT e.empno, e.firstnme, e.lastname,
>   XMLELEMENT ( NAME "foo:Emp",
> XMLNAMESPACES('http://www.foo.com' AS "foo"),
> XMLATTRIBUTES(e.empno as "serial"),
>   e.firstnme,
>   e.lastname ) AS "Result"
>FROM EMP e
>WHERE e.edlevel = 12;
>  
> The NAMESPACES function is not supported by Postgres in the XMLELEMENT 
> function. Is there any way to get this to work? I’ve looked at the WITH 
> syntax but it doesn’t look like that will be helpful here.
>  
>  
> Which Postgres version?  Have you tried path function?
> Regards,
> Garfield

https://www.postgresql.org/docs/12/functions-xml.html#FUNCTIONS-XML-PROCESSING

Re: Frequetly updated partial index leads to bloat on index for Postresql 11

[Peter Geoghegan]

> On Thu, Mar 18, 2021 at 6:51 AM Tom Dearman  wrote:
> Is this a known issue, are they any ways around it, and if it is an
> issue is there a plan to fix it if a fix is possible?

On second thought I do think that the improvements to 14 will fix this
for you. See the test case here:

https://www.postgresql.org/message-id/flat/CAL9smLC%3DSxYiN7yZ4HDyk0RnZyXoP2vaHD-Vg1JskOEHyhMXug%40mail.gmail.com#e79eca5922789de828314e296fdcb82d

--
Peter Geoghegan

Re: Using XMLNAMESPACES with XMLEMENT

[Pavel Stehule]

Hi

pá 24. 9. 2021 v 23:44 odesílatel Garfield Lewis 
napsal:

> Hi All,
>
>
>
> I am attempting to port the following statement from DB2z to Postgres:
>
>
>
> SELECT e.empno, e.firstnme, e.lastname,
>
>   XMLELEMENT ( NAME "foo:Emp",
>
> XMLNAMESPACES('http://www.foo.com' AS "foo"),
>
> XMLATTRIBUTES(e.empno as "serial"),
>
>   e.firstnme,
>
>   e.lastname ) AS "Result"
>
>FROM EMP e
>
>WHERE e.edlevel = 12;
>
>
>
> The NAMESPACES function is not supported by Postgres in the XMLELEMENT
> function. Is there any way to get this to work? I’ve looked at the WITH
> syntax but it doesn’t look like that will be helpful here.
>

I am afraid this is not supported in Postgres. The XMLNAMESPACE clause can
be used only in XMLTABLE function. You need to make XML and in the next
step you need to modify it as string with string operation.

It can be an interesting feature, and if it is supported by libxml2, then
it can be easily implemented. But at this moment it is unsupported, and you
have to use string operations - it should not be hard to use regexp.

Regards

Pavel




>
> Regards,
>
> Garfield
>