![]() Then I’ll join to inspections and include any inspections than which no inspection is later (i.e. With no lateral joins, my thought process goes like this: Since we want one row per restaurant, I’m going to say FROM restaurants. This is the kind of thing that is easy to express in English, and is a completely reasonable request for a report, but is hard to implement. So each restaurant has a history of inspections, and each inspection can have zero or more violations.įirst, you want to show a list of restaurants with the date and score of their most recent inspection. It has three tables: restaurants -< inspections -< violations The original question is about an app that shows restaurant inspection results. ![]() Lateral joins can be incredibly useful when you need them, but it’s hard to grok their “shape” without a concrete example. I hope that is ok.A question came up on the pdxruby mailing list that is a great example for Postgres’s new LATERAL join feature. I changed the names and removed some data to make it more understandable and anonymous. Xxx.events for each row execute function _timescaledb_internal.insert_blocker() Īlter table xxx.events set(press, press_segmentby = 'machine_id') īy the way, the tables in my DB have other names and more columns. Xxx.positions for each row execute function _timescaledb_internal.insert_blocker() Īlter table xxx.positions set(press, press_segmentby = 'machine_id') Īnd the SQL for the event data: - Drop tableĬONSTRAINT events_machine_id_fkey FOREIGN KEY (machine_id) REFERENCES xxx.machine_instance(id)ĬREATE UNIQUE INDEX idx_events ON xxx.events USING btree (machine_id, "time") Here are the SQL for the HF table: - Drop tableĬONSTRAINT positions_machine_id_fkey FOREIGN KEY (machine_id) REFERENCES xxx.machine_instance(id)ĬREATE INDEX idx_positions ON xxx.positions USING btree (machine_id, "time" DESC) > Seq Scan on compress_hyper_18_142_chunk (cost=0.00.9.41 rows=353 width=99)ĭo you have an explaination for that? Is there a way I can do the query faster? > Custom Scan (ChunkAppend) on position (cost=.85 rows=19735000 width=23) Merge Cond: (position."time" = event_data."time") ![]() ![]() I looked at the querry plan in detail and it looks like for my initial query, only a join is executed and not a left join which exactly explains my results.Įxplain for original query: Merge Join (cost=6.97 rows=2697607 width=16) I thought it is smart to access both tables by their index. Your guess was right, I get the right result.ĭrawback is that it takes more time because I dont use any index (which is on machine_id and time) for the event table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |