StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You have a normalisation problem.</p> <ul> <li>timeslice_id is a surrogate key.</li> <li>(feature_id, sequence_number} are a candidate key</li> <li>(feature_id, valid_time_begin ( valid_time_end) ) is also a candidate key.</li> </ul> <p>You are misusing the windowing function, just to pick the candidate with rank=1. A self-join is probably cheaper.</p> <p>EDIT:</p> <pre><code>CREATE index feature_timeslice_alt2 ON feature_timeslice ( feature_id,valid_time_begin); CREATE UNIQUE index feature_timeslice_alt ON feature_timeslice ( feature_id,sequence_number); CREATE VIEW feature_timeslice_id_encore AS SELECT timeslice_id FROM feature_timeslice t0 WHERE (current_timestamp AT TIME ZONE 'UTC', '0'::interval) OVERLAPS (t0.valid_time_begin, COALESCE(t0.valid_time_end, 'infinity'::timestamp)) AND NOT EXISTS ( SELECT timeslice_id FROM feature_timeslice t1 WHERE (current_timestamp AT TIME ZONE 'UTC', '0'::interval) OVERLAPS (t1.valid_time_begin, COALESCE(t1.valid_time_end, 'infinity'::timestamp)) -- EDIT: forgot this AND t1.feature_id = t0.feature_id AND t1.sequence_number < t0.sequence_number ); </code></pre> <p>EDIT: the resulting query plan:</p> <pre><code>------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Hash Anti Join (cost=9090.62..18428.34 rows=45971 width=4) (actual time=110.053..222.897 rows=9030 loops=1) Hash Cond: (t0.feature_id = t1.feature_id) Join Filter: (t1.sequence_number < t0.sequence_number) -> Seq Scan on feature_timeslice t0 (cost=0.00..8228.67 rows=68956 width=12) (actual time=0.031..106.646 rows=9030 loops=1) Filter: "overlaps"(timezone('UTC'::text, now()), (timezone('UTC'::text, now()) + '00:00:00'::interval), valid_time_begin, COALESCE(valid_time_end, 'infinity'::timestamp without time zone)) -> Hash (cost=8228.67..8228.67 rows=68956 width=8) (actual time=109.979..109.979 rows=9030 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 353kB -> Seq Scan on feature_timeslice t1 (cost=0.00..8228.67 rows=68956 width=8) (actual time=0.016..106.995 rows=9030 loops=1) Filter: "overlaps"(timezone('UTC'::text, now()), (timezone('UTC'::text, now()) + '00:00:00'::interval), valid_time_begin, COALESCE(valid_time_end, 'infinity'::timestamp without time zone)) Total runtime: 223.488 ms </code></pre> <p>The query plan for the OP query was similar to his's, and had a "Total runtime: 1404.092 ms ". (but it will probably scale worse, cause of the sort step)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload