performance - Postgresql: Select 6M rows - takes 5min? -


when select table below (containing 6 milion rows) takes 5 minutes complete following statement:

select * public.matchmsg order tradeid desc

what doing wrong?

table definition

create table public.matchmsg (   id bigint not null default nextval('matchmsg_id_seq'::regclass),   tradeid bigint not null,   matchdate timestamp time zone,   price double precision not null,   size double precision not null,   issell boolean not null,   constraint matchmsg_pkey primary key (id) ) (   oids=false,   autovacuum_enabled=true ); alter table public.matchmsg   owner mb;  create unique index tradeid_idx   on public.matchmsg   using btree   (tradeid);

update 1:

explain analyse select * public.matchmsg;  "seq scan on matchmsg  (cost=0.00..117720.03 rows=6000103 width=41) (actual time=0.041..837.495 rows=6000102 loops=1)" "planning time: 1.769 ms" "execution time: 914.932 ms"

is query supposed execute in 117 sec?

update 2:

explain analyse select * public.matchmsg order tradeid  "index scan using tradeid_idx on matchmsg  (cost=0.43..278622.57 rows=6177667 width=41) (actual time=0.172..1465.935 rows=6177670 loops=1)" "planning time: 0.246 ms" "execution time: 1553.377 ms"

update 3:

to right , proper answer have ask right question...and may didnt... realized selecting 6m rows not make sense...but fact did - , didnt understand why pc slow give me proper response (and still dont - no matter of db, ui or ever - think 5 min way slow - im using postgres own native tools: pg enterprise m. or pg pgadminiii - identical). tools faster/better, guys use postgresql?

i realize index doesnt thing - when select rows , columns.

i tried run cluster statement - didnt change - far tell...?

what im going 6m rows? - im going lot of different analysis algorithm/patterns on time... depending on math/expressions need , how difficult/tough calculate ect... think have 2 options here:

either needed math via sql in db or load 1000 rows(only column/s) nodejs code, math on given loaded data...loop through entire db...

to info follow statements perform this:

select sum(price) matchmsg --> 1 sec!  select tradeid matchmsg order tradeid asc limit 1000 offset 5000000 --> 1 sec!

i tried execute 

"select * matchmsg"

inside nodejs - after 30 sec or crash - error desc: 

"fatal error: call_and_retry_last allocation failed - process out of memory".

but far more interesting fact that:

"select tradeid matchmsg" --> 7 sec!

and pick out resultset[5000000] , tradeid @ position within 7 sec inside nodejs app...

all these fact make in end of day - have down real math understand way go...

this normal behavior. in such cases main problem fact user has wait lot. might consider repeated sending of chunks of data, show part of data, while whole data being loaded. also, might consider using paging, more-or-less standard, waiting time such task pretty normal.


Comments

Post a Comment

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

Image
i have page in each contest shown in row. each row contains contest id , link other model names have foreign key contest. news model: class news(models.model): title = models.charfield(max_length=50, null=true) description = models.textfield() time = models.timefield( default= datetime.datetime.now().time()) contest = models.foreignkey(contest) i want use admin adding , changing these models. want when user clicks on 'news' has foreign key contest id=1, shows list of news value foreign key. use {% instance in instances %} <tr class="{% cycle 'row1' 'row2' %}"> <td>{{ instance.title }}</td> <td>{{ instance.starttime }}</td> <td>{{ instance.timespan }}</td> <td>{{ instance.date }}</td> <td><a href="/adm...
Read more

android - Keyboard hides my half of edit-text and button below it even in scroll view -

i having trouble keyboard covers edit-text , half of button in scroll view. here's layout , please tell me how should resolve problem. many questions on topic none of them s working me. <?xml version="1.0" encoding="utf-8"?> <linearlayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:background="@drawable/bg" android:layout_height="match_parent"> <scrollview android:layout_width="match_parent" android:fillviewport="true" android:layout_height="match_parent"> <relativelayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="vertical"> <linearlayout android:layout_width="match_parent" android:layout_height="w...
Read more

css - Make div keyboard-scrollable in jQuery Mobile? -

from can tell, although jquery-mobile-powered pages can contain divs overflow set scroll or auto, , these divs can scrolled one-screen bar or mouse wheel, cannot scrolled using arrow keys, page-up/page-down, or home/end. instead, official "page" div (with data-role="page") absorbs input. perhaps other divs can't acquire focus, i'm not sure. is there way around this? edit: jsfiddle of simple example: https://jsfiddle.net/qogz0shx/ <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"> </script> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"/> <style> #outer { overflow:scroll; height: 50vh; width: 50vw; } #inner { height: 500vh; width: 500vw; } ...
Read more