Hi Andy, Yeah, the table scan was what worried me. As for no indexes ? I just didn't put the "create index" statements in my post ... ;-) Tim On 6 January 2015 at 18:35, Andy Colson <andy@xxxxxxxxxxxxxxx> wrote: > On 1/6/2015 12:02 PM, Tim Smith wrote: >> >> Hi, >> >> I'm probably being incredibly stupid and missing something incredibly >> simple but I've got a bit of query-writers block here ! >> >> create table app_sessions( >> session_id char(64) unique not null, >> user_id char(32) unique not null, >> session_start bigint not null, >> session_lastactive bigint not null >> ); >> >> >> The rules are : >> Enforced session timeout after 86400 seconds (1 day) >> Last active less than 1 hour ago. >> >> My idea to clean out stale sessions : >> delete from app_sessions where extract (epoch from >> now())-session_start>86400 or session_lastactive<=extract (epoch from >> now())-3600; >> >> But of course that's going to be a nasty query, so that's why I think >> I'm missing something and need a fresh pair of eyes (or a whole >> mailing list's worth of eyes !!). >> >> Thanks guys ! >> >> > > I don't see any other way. Why do you think it'll be so nasty? Cuz it'll > table scan? You have no indexes so it doesn't matter what you write, it'll > have to scan all rows. How many rows do you expect to have? 500? 1000? > Table scan will be fine. > > If you wanted to make it more readable .. but work the same, you could use > timestamptz instead of bigint, and then write: > > where current_timestamp - '1 day'::interval < session_start > > -Andy > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
