0

I have the following table in postgresql 9.5 database on ubuntu 16 (4 gb ram, 2vCPU):

create table stat_data (
    name varchar NOT NULL,
    the_date date NOT NULL,
    uopen numeric,
    uhigh numeric,
    ulow numeric,
    uclose numeric,
    uvolume numeric,
    dividends numeric,
    splits numeric,
    aopen numeric,
    ahigh numeric,
    alow numeric,
    aclose numeric,
    avolume numeric,
    PRIMARY KEY(name, the_date)
);

It contains 3.2 mln rows. Then I'm running the following script:

SELECT name, the_date, uclose, uvolume from stat_data;

Actual time spent to run it was: 6 seconds.

explain (analyze, buffers) on the same query gives:

Seq Scan on stat_data  (cost=0.00..82578.57 rows=3206057 width=20) (actual time=0.100..647.835 rows=3206057 loops=1)    
  Buffers: shared hit=481 read=50037  
Planning time: 0.061 ms  
Execution time: 829.982 ms 
(4 rows)

I also created two indexes for the_date and name. Showing list of indexes below:

CREATE INDEX stat_data_name_idx ON public.stat_data USING btree (name text_pattern_ops) 
CREATE UNIQUE INDEX stat_data_pkey ON public.stat_data USING btree (name, the_date)  
CREATE INDEX stat_data_the_date_idx ON public.stat_data USING btree (the_date)

Then running query:

SELECT name, the_date, uclose, uvolume 
from stat_data 
where the_date >= '2018-01-01' 
order by name asc, the_date desc;

Explain shows:

explain (analyze , buffers) SELECT name, the_date, uclose, uvolume from stat_data where the_date >= '2018-01-01' order by name asc, the_date desc;

Sort  (cost=437046.14..445061.28 rows=3206057 width=20) (actual time=7227.767..7561.816 rows=3206057 loops=1)
   Sort Key: name, the_date DESC
   Sort Method: quicksort  Memory: 348755kB
   Buffers: shared hit=513 read=50005
   ->  Seq Scan on stat_data  (cost=0.00..90593.71 rows=3206057 width=20) (actual time=0.084..829.024 rows=3206057 loops=1)
         Filter: (the_date >= '2018-01-01'::date)
         Buffers: shared hit=513 read=50005
 Planning time: 0.141 ms
 Execution time: 7746.269 ms

Running this query without explain returns result after 12 seconds.

I tried increasing work_mem and shared_buffers, didn't help at all. Also tried desc/asc indexes. Max I could get with them is 8 seconds. Which is still bad.

Maybe I am missing something here? Any serious mistakes?

EDIT 1:

Increased to 50% RAM of shared_buffers param (2GB) and got this:

Sort  (cost=438066.43..446099.34 rows=3213165 width=26) (actual time=7227.034..7562.172 rows=3214320 loops=1)
   Sort Key: name, the_date DESC
   Sort Method: quicksort  Memory: 349423kB
   Buffers: shared hit=50636
   ->  Seq Scan on stat_data  (cost=0.00..90794.56 rows=3213165 width=26) (actual time=0.010..908.545 rows=3214320 loops=1)
         Filter: (the_date >= '2018-01-01'::date)
         Buffers: shared hit=50630
 Planning time: 1.329 ms
 Execution time: 7748.418 ms

Seems like no more read (shared hits only). But still slow. Maybe CPU usage is 100%? Possible to reduce that?

  • Few questions. How many columns in the table? What is max data size of single row all columns? What is max data size of single row and the three columns? What is table size? – Rajesh Ranjan Jul 11 at 15:49
  • @RajeshRanjan it is in the question: It contains 3.2 mln rows. – McNets Jul 11 at 15:53
  • @McNets That's number of records in the table not size(how much space it'll take on disk or in memory). – Rajesh Ranjan Jul 11 at 15:57
  • 1
    It seems the condition on the_date doesn't filter out any rows, so the Seq Scan is the best option to retrieve all those rows. – a_horse_with_no_name Jul 11 at 16:25
  • 1
    A part of your observed timings (6 s vs 829 ms and 12 s vs 7.748 s) seems to come from either the client (rendering the output) or transmitting them from the server. – dezso Jul 12 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.