log☇︎
105000+ entries in 0.415s
phf: i treat the exercise as a chinese room wired to explosives. making a mistake elevates danger level, passage of time elevates danger level, there's a random factor when it's going to blow anyway, etc.
mircea_popescu: no, by all means, i like watching the "alf encounters technology" hour :D
asciilifeform: ty phf , trinque , mircea_popescu
asciilifeform: amazing, how many old rusty razors this steamroller has randomly glued to the controls, to the seat, to the floor...
phf: but there's only so few rabbis i can pull out of a hat
phf: perhaps try select * from moduli left outer join (select distinct unnest(mods) as mod from factors) as foo on moduli.idx = foo.mod where foo.mod is null
mircea_popescu: because he's doing what i told you to do discreetely.
asciilifeform: oh hey, phf's algo appears to work
phf: asciilifeform: is the original query faster than the most last rewrite? asking for personal enlightenment
mircea_popescu: just don't expect any backing to it for recursive elegance. it dun have what with.
asciilifeform: mircea_popescu: if not too many cans, and compass points in just the right direction and planets aligned...
trinque: you came in asking for help with the one you already stuck dick in.
trinque: nobody's here to defend sql to you, fucks sake
mircea_popescu: asciilifeform, dude. it's a tool. like a steamroller. it does the following thing well : if you have cans, it makes them flat.
asciilifeform: mircea_popescu: this'd be in the actual cruncher, a c proggy
mircea_popescu: the only way out is if he ever gives up trying to ask sql for lisp support and instead writes his python around the sql like god meant it.
asciilifeform: trinque: i'ma stop polluting, at this point, the logs, and solve the problem programmatically outside of the abjectly retarded sqltron
phf: you can keep from writing to disk by doing a materialized view. do a materialized view for your temp query, and then run second query against it. that would be my last resort though
trinque: I'm backing away. asking us to optimize a query in isolation for you without the explain and knowing nothing of your schema is ridiculous
asciilifeform: entirely sinks the thing.
asciilifeform: i do. not. want. to. write. to. disk.
mircea_popescu: asciilifeform, " select * from moduli where idx not in (select distinct unnest(mods) from factors)" << 1.make a new table ; 2.run "select distinct unnest(mods) from factors" and dump the result into this new table ; 3. index it by idx ; 4. run your select as an outer join between moduli and the new table.
phf: asciilifeform: oh there's a type
trinque: and how to read it?
asciilifeform: ( naturally there are more results, by factor of 1,000 , in the 'not in ....', but outputting all 9+ mil mods from db without ~any~ filtration, takes about 7min )
mircea_popescu: phf, possibly, i'm not really THAT close to the core of that beast.
phf: mircea_popescu: i'm not sure that the problem is the recalculate, i think the problem is actually not in in combination with temp calculate
asciilifeform: i still don't grasp why the everliving fuck a 'not in ...' should be any slower than an 'in ....'
trinque: lol yes, that's what star means
asciilifeform: trinque: all of them.
mircea_popescu: that's the only available juice to squeeze there, communicate to the engine that your db is slow, the tmp doesn't move. and you do this by not making it a tmp.
asciilifeform: phf: eggog, missing FROM-clause entry for table "module" LINE 1: ...tors) select * from moduli left outer join foo on module.idx...
trinque: asciilifeform: what are the cols there that you want
mircea_popescu: phf, i expect it'll still re-calc the temp on every pass.
phf: mircea_popescu: maybe try this with foo as (select distinct unnest(mods) as mod from factors) select * from moduli left outer join foo on module.idx = foo.mod where foo.mod is null;
mircea_popescu: it's the anti-lisp.
trinque: asciilifeform: what's the shape of *
mircea_popescu: a db is a good tool for pre-given solutions for some kinds of problems. it is not a tool for implementing arbitrary expressivity.
asciilifeform: what am i missing, does mircea_popescu know of another way of thinking, that actually solves the problem with the available tooling at reasonable cost ?
mircea_popescu: yes dood, that's why i said what i said re your way of thinking.
asciilifeform: this is ruinously expensive when the dataset is 10s of GB.
mircea_popescu: but instead actually build the table you're trying to not-in, and do a join operation.
mircea_popescu: no, that was his previous suggestion. what i mean by ACTUAL is that you don't use tmp
asciilifeform: ( 'build the table and do join' )
asciilifeform: mircea_popescu: i thought your algo involved a temporary table
asciilifeform: trinque, mircea_popescu : how would this look in the given item ?
mircea_popescu: (andf said it too fast to have tried it)
trinque: except operator might be faster than "not in"
phf: asciilifeform: other option is to do a left outer join, and then filter by nulls, but i'm trying to recreate the exercise at home so that we're not doing tedious roundtrip through irc
asciilifeform: so far errything i tried, gives same result ( query that promises to run in months, rather than the allotted ~halfhr)
asciilifeform: this was the q to begin with
asciilifeform: how wouldja unroll this ?
mircea_popescu: well evidently not enough, unroll the not-in thing.
asciilifeform: mircea_popescu: right, it is designed for ( what i know of ) the db, and thereby lacks many knobs that i'd dearly like to have
mircea_popescu: but anyway. if your proggy includes a db you will have to ~DESIGN~ for the db.
asciilifeform: phf your query's been running since you gave it to me, but produced sadly 0 output
asciilifeform: ( and if it were to finally rewrite it, i'd ditch sqlism forever and entirely, like a bad dream )
mircea_popescu: now to be clear, it has other issues.
mircea_popescu: incidentally, the inept way in which postgress handles this case is why mysql still official db for mp-wp
asciilifeform: i.e. idiocy that is not in any way implicit in the mathematical structure of the underlying primitives (e.g. btree) but comes from agglomeration of programmers dropped as children
asciilifeform: mircea_popescu: actually 'looked in' plenty, and always run into this nonsense
mircea_popescu: you will soon discover your heavily recursive way of thinking is deeply inadequate for the strengths and useful work of a db ; which may explain why you've not been looking into them to this advanced age.
asciilifeform: needs to run in-place.
asciilifeform: lit suggests that 'not in' operation in postgres is pathologically slow
mircea_popescu: asciilifeform, build the actual table and do a join then.
mircea_popescu: asciilifeform, temp table neh.
phf: oh i suppose with tmp as (select distinct unnest(mods) as mods from factors) select * from moduli where idx not in (select mods from tmp);
asciilifeform: i suspect this dunwork on postgres ?
asciilifeform: LINE 1: ...ds) from factors) select * from moduli where idx not in tmp;
asciilifeform goes to try this..
phf: with tmp as (select distinct unnest(mods) from factors) select * from moduli where idx not in tmp;
asciilifeform: for completeness of picture, 'mods' is a column in factors, which contains indices of moduli (in 'moduli' table) that said factor is a factor of.
asciilifeform: ( all i want above, is to produce a select of all unphuctored moduli. )
asciilifeform: blew hours on this, with no result.
asciilifeform: is it possible to rewrite this so that postgres doesn't do the retarded thing that it evidently does ( this being, to compute the mods in the () , again and again FOR EVERY ROW in moduli )
asciilifeform: i have a proggy which wants to select * from moduli where idx not in (select distinct unnest(mods) from factors) ; but this runs in geological time. for no afaik good reason.
asciilifeform: hey trinque -- or other serious sqlist -- around ?
BingoBoingo wonders what gabriel_laddel is up to, and how the foot is.
mircea_popescu: (as "that length of knife blade that hurts but doesn't seriously injure, obtained by holding the instrument a certain way so the thumb covers the blade". PRISON SLANG!)
mircea_popescu: BingoBoingo, amusingly, no further than today walking through town i was explaining to girl the source and meaning of pulgada
asciilifeform: painfully small display, tho, at least for grown man's eyes
asciilifeform: ( believe or not, thing seems to contain some sort of 3d gpu )
asciilifeform: in principle you could, then, put even eulora, on the c101.
mircea_popescu: extant item slated for replacement anyways, hence all the protocol talk.
mircea_popescu: it can use w/e the fuck you want it to use.
asciilifeform: ( not suggesting to put ~server~ on arm, lol )
mircea_popescu: you mean the client ?
mircea_popescu: the plane ticker fuzz factor is 1-200 anyways.
asciilifeform: there is afaik no adult rng on board ( in what, exactly, is there one ? ) but it has usb socket for FG; and the typical shit webcam for gurl-powered rng in principle.
mircea_popescu: basically acquisition order would switch from "send plane ticket" to "send plane ticket + gpgstation".
asciilifeform: theoretically has moar horse than asciilifeform's beloved x60 , even
mircea_popescu: atm i'm not really doing that ; but for more respectable cut of girlmeat there's ye olde ibms.
asciilifeform: thing has all of the (few) virtues of the crapple (e.g. 9 hrs on battery, 16 days of suspend , charge from usb ) , and , in principle, none of the barfology
asciilifeform: better gift than crapple, verily
mircea_popescu: i'd buy some, give the visiting sluts. sorta like the titbits thing.
asciilifeform: mircea_popescu: it's starvation-cheap. sorta the appeal. 1 to 2 hundy usd
a111: Logged on 2018-04-24 23:41 asciilifeform: mircea_popescu, diana_coman , et al, other folx who travel -- i'd like to get a picture of whether there is 'market' in l1/l2 for a pre-engentooated ( laugh at the laddel-ism, but it promises to be a somewhat painful process involving crocodile and eeprom writer ) 'c101pa' rockchip lappy .
mircea_popescu: http://btcbase.org/log/2018-04-24#1805245 << if it's something i can gift to newbies and it'll allow them to gpg ; and not very expensive ; probably. ☝︎
a111: Logged on 2018-04-24 19:28 phf: diana_coman: is the protocol the entirety of documentation for client writers or there's more stuff in the pipeline?
mircea_popescu: http://btcbase.org/log/2018-04-24#1805229 << yes, the idea is to develop that one item into the complete story for client writers. ☝︎