Check-in [84130ab6bf]
Logged in as anonymous
Overview
Comment:force cast to utilize index
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 84130ab6bfcc1a173153812e634d6df0ffbdf3c01b56113703224336d5e21874
User & Date: arcade on 2020-11-20 16:23:46.657
Other Links: manifest | tags
Context
2020-11-24
09:41
add start message check-in: 8db3dfecf8 user: arcade tags: trunk
2020-11-20
16:23
force cast to utilize index check-in: 84130ab6bf user: arcade tags: trunk
2020-11-19
17:43
0.1.5: logging, mostly check-in: ec616a2a43 user: arcade tags: trunk
Changes
Unified Diff Ignore Whitespace Patch
Modified rsstg.sql from [37cfc2ad41] to [06336e3838].
︙︙
32
33
34
35
36
37
38
39
40
41
42
43

create index rsstg_post__posted_hour on rsstg_post(posted,hour);

create or replace view rsstg_order as
	select source_id, coalesce(last_scrape + make_interval(0,0,0,0,0,(60 / (coalesce(activity, 1)/7 + 1) )::integer), now() - interval '1 minute') as next_fetch
		from rsstg_source natural left join
		(select source_id, count(*) as activity
			from rsstg_post where 
				hour = extract('hour' from now())
				and posted > now() - interval '7 days'
			group by source_id) as act
		where enabled
		order by next_fetch;








|





32
33
34
35
36
37
38
39
40
41
42
43

create index rsstg_post__posted_hour on rsstg_post(posted,hour);

create or replace view rsstg_order as
	select source_id, coalesce(last_scrape + make_interval(0,0,0,0,0,(60 / (coalesce(activity, 1)/7 + 1) )::integer), now() - interval '1 minute') as next_fetch
		from rsstg_source natural left join
		(select source_id, count(*) as activity
			from rsstg_post where 
				hour = extract('hour' from now())::smallint
				and posted > now() - interval '7 days'
			group by source_id) as act
		where enabled
		order by next_fetch;