0

My table create script is below, My table is partitioned by month wise based on saletime. Average one day contains 3 Million records. Totally My table contains 1 Billion Records

CREATE TABLE MyTable(
Id bigint not null,
Col1 text,
Col2 text,
Col3 text,
Col4 text,
Col5 text,
Col6 text,
Col7 text,
Col8 text,
Col9 text,
Saletime timestamp without timezone,
CONSTRAINT pk_tbl_mytable PRIMARY KEY(id,saletime)
Usint INDEX TABLESPACE “MY_TS”
)PARTIOTION BY RANGE(Saletime)
TABLESPACE “MY_TS”;

My Query is below

with CTE AS(
SELECT Id,SaleTime from tbl_search_log
where saletime>’2019-11-01 00:00:00’ and saletime<’2019-12-30 00:00:00’
and siteid in(708,805,432,403)
order by saletime desc limit 1000 offset 0
)
Select S.Id,col1,col2,col3,col4,col5,col6,col7,col8
from mytable S
join CTE on S.id=CTE.Id
where S.saletime>’2019-11-01 00:00:00’ and S.saletime<’2019-12-30 00:00:00’
order by S.saletime desc
limit 1000;

When I use the above query to select 1000 records it takes almost 35 to 40 Seconds My query Explain(Analyze,Buffers) log is below

Limit (cost=10924.12. 10926.62 rows=1000 width=209) (actual time=D40650.143..40650.302 rows=1000 loops=1) "

Buffers: shared hit-3034 read-2120"

Sort

(cost=10924.12. . 10926.62 rous-1000 width=209) (actual time=40650.142.40650.212 rows=1000 loops=1)"

Sort Key saletime DESOM

Sort Method: quicksort Memory: 290KB" Buffers: shared hit=3034 read=2120"

Nested Loop (cost=8146.68.. 10874.29 rows=1000 width=209) actual time=D63.635..40643.680 rows=1000 loops=1)" Buffers shared hit=3034 read=2120

Limit (cost=8146.12..8262.79 rows=1000 width=16 actual time=13014 16.019 rows=1000 loops=1)

Buffers: shared hit=154"

-> Gather Merge (cost=8029.44.19796.80 rows=100856 width=16) actual time=12.954..15.511 rows=2000 loops=1)"

workers Planned: 2

Workers Launched: 2

Buffers: shared hit=168"

Sort (cost=7029.42..7155.49 rous=50428 width=16) (actual time-0.872..1.273 roMs=667 loops=3) "

Sort Keys Plc log 201911. saletime DESC Sort Method: top-N heapsort Memory: 190KB" Horker 0: Sort Method: quicksort Memory 25kB

Horker 1: Sort Method: quicksort Memory: 25KB"

Buffers: shared hit=168

Sort (cost=7029.42..7155.49 rous=50428 width=16) (actual time-0.872..1.273 roMs=667 loops=3) "

Sort Keys Plc log 201911. saletime DESC Sort Method: top-N heapsort Memory: 190KB" Horker 0: Sort Method: quicksort Memory 25kB

Horker 1: Sort Method: quicksort Memory: 25KB"

Buffers: shared hit=168

Parallel Index Only Scan using purlog 201911 p_rlog 201911 (cost=0.56..4012.37 rows=50428 width=16) (actual time=0.009..0.517 rows=2786 loops=3)" Index Cond: (siteid = ANY (4708, 805, 345, 403 :: integer [])) AND (saletime 2019-11-01 00:00:00 : : timestamp without time zone) AND

(saletime < '2019-11-30 00:00:00 : timestamp without

time zone))"


Heap Fetches: 0"

Buffers: shared hit=154" ->

Index Scan using p_mytablelog201911 pkey on p_mytablelog201911 s cost=0.56..2.59 rows=1 width=153) (actyal time=40.599..40.602 row3=1 loops=1000) 
Index Cond: ((id = p rlog 201911.id) AND (saletime >= 2019-11-01 00:00:00 Btimestamp without time zone) AND
(saletime <= 2019-11-30 00:00:00 :: timestamp without time zone)) " 
Buffers:shared hit=2880 read=2120
planing time:0.800 ms
Execution Time: 40651.003 ms

Please suggest me to solve this performance issue.

tbl_search_log and MyTable has almost 1 Billlion Records. tbl_search_log gives the result with in 1 second. But My Table only takes more time.

Note: I am using Windows Server OS with PostgreSQL 12 32 GB RAM Work_mem 1GB Statistics 10000 Random_Page_Cost 1.0 Shared_Buffers 8GB

John
  • 25
  • 8
  • 1
    Should your primary key just be on `id` with a separate index for `saletime`? – Alan Birtles Jan 01 '21 at 18:38
  • The query performance doesn't depend on the number of resulting rows but on the numbers of rows that are in the source tables. Could you give estimates about the number of rows in the tables tbl_search_log and mytables? – Michal T Jan 01 '21 at 20:14
  • @MichalT tbl_search_log and MyTable has almost 1 Billlion Records. tbl_search_log gives the result with in 1 second. But My Table only takes more time. – John Jan 02 '21 at 01:50
  • 1
    Your explain plan is a mess. Could you redo it to avoid random character substitutions and extraneous white space, and to preserve indentations? – jjanes Jan 02 '21 at 02:31
  • What happens if you change it to `... fromCTE join mytable S on S.id=CTE.Id and S.saletime>’2019-11-01 00:00:00’ and S.saletime<’2019-12-30 00:00:00’` – Bohemian Jan 02 '21 at 05:05
  • Could you post a clean explain plan without the character substitutions? – Frank Heikens Jan 02 '21 at 11:10
  • I think the CTE you are using still has a massive amount of rows as it seems it is one month of a table with several billion of rows. This means that you are doing a join on a memory table (CTE) without index on the id column. If this is the case then I would suggest building a temporary table with a proper index on the id column instead of the CTE. Also I don't see why you are using the CTE at all and not joining mytable with tbl_search_log directly. – Michal T Jan 02 '21 at 12:09

0 Answers0