Re: To prefer sorts or filters in postgres, that is the question....

[Bob Jones]

>
> At a short glance, I'd say that they are pretty much the same.
> The filter and the top-1-sort will both require a single scan through
> the result set and one operation per row found.
> And the recursive queries are pretty similar, right?
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com


Thanks Laurenz.

After sending my original message, I did briefly reconsider things.

My current thinking is that the filter is a bit like an "fgrep" and
the sort actually requires memory allocation and some "real work", and
thus I've settled on the filter for now pending experiments with a
larger quantity of data.

To prefer sorts or filters in postgres, that is the question....

[Bob Jones]

Hi,

I've been playing around with hierarchical queries a bit and one thing
I wanted to do is build a query that gives the ultimate parent for a
given child.

The two queries below seem to be a good a shortlist as any.

I'm no query-plan guru, but these seem to be  largely identical aside
from one uses "filter IS NULL" and the other uses "top-N heapsort".

Would there be a reason to prefer one over the other (or perhaps
there's an altogether more efficient way of doing this query ?!?).
My gut-instinct says the sort version ?

=> explain analyze with recursive cte(cmenu_id,depth,cmenu_parent) as (
SELECT cmenu_id,1 as depth,cmenu_parent
FROM cms_menu
WHERE cmenu_id='CHILDNODENAME' and cmenu_active=true
UNION ALL
SELECT c.cmenu_id,cte.depth-1,c.cmenu_parent
FROM cms_menu c
JOIN cte ON c.cmenu_id=cte.cmenu_parent WHERE cmenu_active=true)
select * from cte order by depth LIMIT 1;

QUERY PLAN
---
 Limit  (cost=166.59..166.59 rows=1 width=68) (actual
time=0.132..0.132 rows=1 loops=1)
   CTE cte
 ->  Recursive Union  (cost=0.15..165.31 rows=51 width=68) (actual
time=0.023..0.070 rows=4 loops=1)
   ->  Index Scan using cms_menu_cmenu_id_key on cms_menu
(cost=0.15..8.17 rows=1 width=68) (actual time=0.022..0.022 rows=1
loops=1)
 Index Cond: (cmenu_id = 'CHILDNODENAME'::text)
 Filter: cmenu_active
   ->  Hash Join  (cost=0.33..15.61 rows=5 width=68) (actual
time=0.009..0.010 rows=1 loops=4)
 Hash Cond: (c.cmenu_id = cte_1.cmenu_parent)
 ->  Seq Scan on cms_menu c  (cost=0.00..14.40
rows=220 width=64) (actual time=0.002..0.004 rows=12 loops=3)
   Filter: cmenu_active
 ->  Hash  (cost=0.20..0.20 rows=10 width=36) (actual
time=0.002..0.002 rows=1 loops=4)
   Buckets: 1024  Batches: 1  Memory Usage: 8kB
   ->  WorkTable Scan on cte cte_1
(cost=0.00..0.20 rows=10 width=36) (actual time=0.000..0.001 rows=1
loops=4)
   ->  Sort  (cost=1.28..1.40 rows=51 width=68) (actual
time=0.131..0.131 rows=1 loops=1)
 Sort Key: cte.depth
 Sort Method: top-N heapsort  Memory: 25kB
 ->  CTE Scan on cte  (cost=0.00..1.02 rows=51 width=68)
(actual time=0.024..0.073 rows=4 loops=1)
 Planning time: 0.221 ms
 Execution time: 0.163 ms
(19 rows)




=>explain analyze with recursive cte(cmenu_id,cmenu_parent) as (
SELECT cmenu_id,cmenu_parent
FROM cms_menu
WHERE cmenu_id='CHILDNODENAME' and cmenu_active=true
UNION ALL
SELECT c.cmenu_id,c.cmenu_parent
FROM cms_menu c
JOIN cte ON c.cmenu_id=cte.cmenu_parent WHERE cmenu_active=true)
select * from cte where cmenu_parent IS NULL LIMIT 1;

QUERY PLAN
---
 Limit  (cost=165.19..166.21 rows=1 width=64) (actual
time=0.069..0.069 rows=1 loops=1)
   CTE cte
 ->  Recursive Union  (cost=0.15..165.19 rows=51 width=64) (actual
time=0.020..0.064 rows=4 loops=1)
   ->  Index Scan using cms_menu_cmenu_id_key on cms_menu
(cost=0.15..8.17 rows=1 width=64) (actual time=0.019..0.020 rows=1
loops=1)
 Index Cond: (cmenu_id = 'CHILDNODENAME'::text)
 Filter: cmenu_active
   ->  Hash Join  (cost=0.33..15.60 rows=5 width=64) (actual
time=0.011..0.012 rows=1 loops=3)
 Hash Cond: (c.cmenu_id = cte_1.cmenu_parent)
 ->  Seq Scan on cms_menu c  (cost=0.00..14.40
rows=220 width=64) (actual time=0.003..0.005 rows=9 loops=3)
   Filter: cmenu_active
 ->  Hash  (cost=0.20..0.20 rows=10 width=32) (actual
time=0.002..0.002 rows=1 loops=3)
   Buckets: 1024  Batches: 1  Memory Usage: 9kB
   ->  WorkTable Scan on cte cte_1
(cost=0.00..0.20 rows=10 width=32) (actual time=0.001..0.001 rows=1
loops=3)
   ->  CTE Scan on cte  (cost=0.00..1.02 rows=1 width=64) (actual
time=0.068..0.068 rows=1 loops=1)
 Filter: (cmenu_parent IS NULL)
 Rows Removed by Filter: 3
 Planning time: 0.302 ms
 Execution time: 0.105 ms
(18 rows)

Re: cursor "x" does not exist

[Bob Jones]

On 14 April 2018 at 16:38, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Bob Jones <r.a.n.d.o.m.d.e.v.4+postg...@gmail.com> writes:
>> My apologies if I'm being incredibly stupid here, but I've reviewed
>> what the docs have to say about naming portals and I still can't see
>> where I'm going wrong here ?
>
> I think you're forgetting to double-quote an upper case identifier.
>
>> FETCH ALL IN B;
>> ERROR:  cursor "b" does not exist
>
> The cursor is named "B" not "b", but B without quotes folds to the latter.
>
> regards, tom lane


Thank you tom for showing me the error in my ways.

Now where's that dunce cap gone ?  Maybe I don't deserve to use
anything better than MySQL.   ;-)

Recursive CTE for building menus

[Bob Jones]

Hello,

Whilst researching current thinking on hierarchical queries in
Postgres, I stumbled accross this excellent blog post:

https://illuminatedcomputing.com/posts/2014/09/postgres-cte-for-threaded-comments/

But try as I might, my SQL-foo is not up to scratch to adapt it to my
needs, I keep on loosing child nesting and other weird bug-dom.

My table looks like this :
menu_title text
menu_item_id text
menu_priority integer
menu_parent text

The adaptions I am trying to make are as follows:
- Higher priority moves the item higher up the menu (i.e. adapting
from the original "votes" concept).
- Default alphabetical ordering of titles
- Use of alphanumeric IDs instead of numeric

The only thing that I can get consistently working is the alphanumeric menu IDs.

For menu priorities, postgres does not seem to like mixing numeric and
alphanumeric in an array:
ERROR:  ARRAY types integer and text cannot be matched
LINE 3:  array[-menu_priority,menu_itemid] as path,1 as depth

insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Home','H',1000,NULL);
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('About','A',900,NULL);
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('FOOBAR','F',800,NULL);
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Resources','R',NULL,'A');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Background','B',NULL,'A');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Foo','Ff',NULL,'F');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('About Bar','Fba',NULL,'Fb');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Team Bar','Fbt',NULL,'Fb');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Bar','Fb',NULL,'F');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('Foo World','Ffw',NULL,'Ff');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('About World','FFwa',NULL,'Ffw');
insert into test_table(menu_title,menu_item_id,menu_priority,menu_parent)
values('World Introduction','FFwi',1000,'Ffw');

N.B. Although I show NULL as a default priority, I have experimenting
with setting default priorities with no success.

The expected outcome from the above would be (ignore the pretty-print
elements, its just to help human parsing !):
•Home
•About
-> Background
-> Resources
•FOOBAR
-> Bar
->-> About Bar
->-> Team Bar
-> Foo
->-> Foo World
->->-> World Introduction
->->-> About World