0

my table contains 1 billion records. It is also partitioned by month.Id and datetime is the primary key for the table. When I select

select col1,col2,..col8
from mytable t 
inner join cte on t.Id=cte.id and dtime>'2020-01-01' and dtime<'2020-10-01'

It uses index scan, but takes more than 5 minutes to select. Please suggest me. Note: I have set work_mem to 1GB. cte table results comes with in 3 seconds.

Anatoly
  • 20,799
  • 3
  • 28
  • 42
nikitha palani
  • 1
  • 2
  • How about the cte table index? – A l w a y s S u n n y Dec 17 '20 at 03:38
  • CTE table results comes within 3 seconds – nikitha palani Dec 17 '20 at 04:02
  • Please **[edit]** your question and add the [execution plan](https://www.postgresql.org/docs/current/static/using-explain.html) generated using **`explain (analyze, buffers, format text)`** (**not** just a "simple" explain) as [formatted text](http://stackoverflow.com/help/formatting) and make sure you preserve the indention of the plan. Paste the text, then put `\`\`\`` on the line before the plan and on a line after the plan. Please also include complete `create index` statements for all indexes as well. –  Dec 17 '20 at 06:26
  • does cte table has only 2 columns ? if not try this ``` select col1,col2,..col8 from mytable t where id in (select distinct id from cte where dtime>'2020-01-01' and dtime<'2020-10-01') A ; ``` – Syed Mushtaq Dec 17 '20 at 07:59

1 Answers1

0

Well it's the nature of join and it is usually known as a time consuming operation.

First of all, I recommend to use in rather than join. Of course they have got different meanings, but in some cases technically you can use them interchangeably. Check this question out.

Secondly, according to the relation algebra whenever you use join each rows of mytable table is combined with each rows from the second table, and DBMS needs to make a huge temporary table, and finally igonre unsuitable rows. Undoubtedly all the steps and the result would take much time. Before using the Join opeation, it's better to filter your tables (for example mytable based date) and make them smaller, and then use the join operations.

Elyas Hadizadeh
  • 3,289
  • 8
  • 40
  • 54