Re: To prefer sorts or filters in postgres, that is the question....
> > 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....
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
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
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