0

I am using PostgreSQL 12. My Table contains 1 Billion Records. It was partitioned by every Month based on date range. Every day contains more than 3 Million Records. When I select some set of ids, it takes more time.

I want to filter 20 days but it takes very long time. So I select records for one day only. It's also took more than 10 seconds...

Below is my query,

Select id,col1,col2,col3,col4,col5,col6,Logtime from myLog R
where R.id in(1818154 59,…………**500 IDS**………..,180556591)
and R.LogTime='2019-07-29'::date 
and R.Logtime>='2019-07-29 00:00:00' and R.LogTime<='2019-07-30 00:00:00'
Order by R.LogTime desc;

Below is my queries plan,

"Sort (cost=2556.35.2556.35 rows=1 width=298) (actual time 10088.084.10088.091 rows=557 loops-1)"

" Sort Key: R.LogTime DESC”

" Sort Method: quicksort Memory: 172 kB

-> Index Scan using p_my_log201907_LogTime_id_idx on p_mylog201907 r (cost=

0.56..2556.34 rows=1 width-298) (actual time=69.056..10085.712 rows=557 loops=1)"

Index Cond: (Logtime):: date = "2019-07-29’::date)

AND (id = ANY (‘{1818154 59,…………500 IDS………..,180556591}’::bigint[])

Filter: ( Logtime>= ‘2019-07-29 00:00:00’:: timestamp without time 
AND (Logtime < ‘2019-07-30 00:00:00’:: timestamp without time zone)}"

"Planning Time: 0.664 ms
"Execution Time: 10088.189 ms

Below is my index,

CREATE INDEX Idx_LogTime ON MyLog( (LogTime::date) DESC,id desc);

At the time of query execution I have set the WORK_MEM to '1GB'. Please suggest me. How can I optimize and speedup my query?

Mishal
  • 450
  • 9
  • 27
John
  • 25
  • 8
  • 20 days of logs mean 60 million log entries (records). It needs resources in fact. Did you try specify particular partition(s). If you filter for one day it's only one partition but 20 days filter uses max two partitions (child tables). – zforgo Dec 25 '20 at 17:41
  • "Actually I want to filter 20 days but it takes very long time. So I select records for one day only" You should show us the plan for the query you want to speed up, not for some other query that doesn't do what you want anyway. Also, please do `EXPLAIN (ANALYZE, BUFFERS)` and turn on track_io_timing first, if possible. And show us how you wrote the PARTITION BY (are you casting the time to do a date in your partition clause)? – jjanes Dec 25 '20 at 18:16

0 Answers0