sql - Speed up "find or create" postgres query that searches from id and timestamp -


i've got postgres query i'm using "find or create" record, however, taking ~60-80ms complete ~20k rows, , part of loop. running whole loop starting drag out, @ ~20s, , getting longer (the whole loop inside transaction). i'm using market_id , timestamp select rows.

i suspect timestamp field giving me trouble, i'm not sure. i'm using in c.

here how create table:

create table market_summary(   id serial primary key,   market_id int,   market_name varchar(20),   high float,   low float,   volume float,   last float,   base_volume float,   timestamp timestamp,   bid float,   ask float,   open_buy_orders int,   open_sell_orders int,   previous_day float,   market_creation_date timestamp,   created_at timestamp default current_timestamp,   updated_at timestamp default current_timestamp )

and here sample of query i'm using:

insert market_summary(market_id,market_name,high,low,volume,last,base_volume,timestamp,bid,ask,open_buy_orders,open_sell_orders,previous_day,market_creation_date)  select 19, 'btc-bitb', 0.000001, 0.000001, 37359913.914823, 0.000001, 21.940584, '2017-08-18t00:13:43.643', 0.000001, 0.000001, 310, 4255, 0.000001, '2015-02-15t23:07:32.777'    not exists (     select (market_id,timestamp) market_summary        market_id = 19 , timestamp::timestamp = to_timestamp('2017-08-18t00:13:43.643', 'yyyy-mm-ddthh24:mi:ss.ms')   ); select * market_summary      market_id = 19 , timestamp::timestamp = to_timestamp('2017-08-18t00:13:43.643', 'yyyy-mm-ddthh24:mi:ss.ms');

and explain analyze:

    insert on market_summary  (cost=19778.63..19778.69 rows=1 width=170) ->  subquery scan on "*select*"  (cost=19778.63..19778.69 rows=1 width=170)       ->  result  (cost=19778.63..19778.64 rows=1 width=364)             one-time filter: (not $0)             initplan 1 (returns $0)               ->  seq scan on market_summary market_summary_1  (cost=0.00..19778.63 rows=1 width=0)                     filter: ((market_id = 19) , ("timestamp" = to_timestamp('2017-08-18t00:13:43.643'::text, 'yyyy-mm-ddthh24:mi:ss.ms'::text)))

is there way speed significantly?

edit: forgot put in relationship market, market has many marketsummary.

edit 2: market table:

create table markets(   id serial primary key,   name varchar(20),   created_at timestamp default current_timestamp,   updated_at timestamp default current_timestamp )


Comments

Post a Comment

Popular posts from this blog

Is there a better way to structure post methods in Class Based Views -

i'm using class based views in django , never specify url in ajax calls , omit action parameter in form because know these requests go through post method of class based view. notice view - profile page make many requests depending on user updating on page - post method become convoluted , if/else mess example: def post(self, request, *args, **kwargs): # handle user changing available date if request.post.get('availabledate') != none: self.updateavailabledate(request) return jsonresponse({'result':'success'}) if request.post.get('newprojectname') != none: creative_user = creativeuserprofile.objects.get(id = request.user.id) project = project.create_project(creative_user, request.post['newprojectname']) project.save() return jsonresponse({'projectid': project.id }) if request.post.get('projectimage') != none , request.files['file']: proj...
Read more

performance - Why is XCHG reg, reg a 3 micro-op instruction on modern Intel architectures? -

i'm doing micro-optimization on performance critical part of code , came across sequence of instructions (in at&t syntax): add %rax, %rbx mov %rdx, %rax mov %rbx, %rdx i thought had use case xchg allow me shave instruction , write: add %rbx, %rax xchg %rax, %rdx however, dimay found agner fog's instruction tables , xchg 3 micro-op instruction 2 cycle latency on sandy bridge, ivy bridge, broadwell, haswell , skylake. 3 whole micro-ops , 2 cycles of latency! 3 micro-ops throws off 4-1-1-1 cadence , 2 cycle latency makes worse original in best case since last 2 instructions in original might execute in parallel. now... cpu might breaking instruction micro-ops equivalent to: mov %rax, %tmp mov %rdx, %rax mov %tmp, %rdx where tmp anonymous internal register , suppose last 2 micro-ops run in parallel latency 2 cycles. given register renaming occurs on these micro-architectures, though, doesn't make sense me done way. why wouldn't register renamer ...
Read more

c# - Asp.net web api : redirect unauthorized requst to forbidden page -

im trying redirect unauthorized request forbidden page instead i'm getting forbidden page in response body , how can fix ? here's startup class : app.createperowincontext(storecontext.create); app.createperowincontext<applicationusermanager>(applicationusermanager.create); app.createperowincontext<applicationsigninmanager>(applicationsigninmanager.create); app.usecookieauthentication(new cookieauthenticationoptions { authenticationtype = defaultauthenticationtypes.applicationcookie, expiretimespan = timespan.fromdays(30), }); app.useexternalsignincookie(defaultauthenticationtypes.externalcookie); app.usetwofactorsignincookie(defaultauthenticationtypes.twofactorcookie, timespan.fromminutes(5)); app.usetwofactorrememberbrowsercookie(defaultauthenticationtypes.twofactorrememberbrowsercookie); the method im trying reach : [httpget] [authorize(roles = "admin")] public string getcurrentusername() { return userman...
Read more