0

I have a simple query:

SELECT  t1."TimeS" as TimeS, t1."JobID" as JobID, t1."TaskIndex" as TaskIndex,  t1."MachineID" as MachineID ,t2."EventType" as EventType,  t1."UserName" as UserName, t1."CpuReq" as CpuReq, t1."MemReq" as MemReq, 
  t2."TimeS" as FinTime  , (t2."TimeS"-t1."TimeS") as duration
FROM  scheduled_tasks_table  as t1 ,  public.dead_tasks_table as t2 
     WHERE t1. "TaskIndex"= t2. "TaskIndex"
     AND t1."JobID"= t2."JobID" AND (t2."TimeS"-t1."TimeS")>='0' 

I have 15GB memory (RAM) and my Config File is:

max_connections = 200
shared_buffers = 3840MB
effective_cache_size = 11520MB
work_mem = 19660kB
maintenance_work_mem = 960MB
min_wal_size = 1GB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100

Both my tables both have a single primary key and a single indexe as:( TaskIndex, JobId, Timet)

But this query is really slow:

EXPLAIN:

" "Merge Join  (cost=19982628.08..1436656715.61 rows=20251904912 width=105)"
"  Merge Cond: ((t1."TaskIndex" = t2."TaskIndex") AND (t1."JobID" = t2."JobID"))"
"  Join Filter: ((t2."TimeS" - t1."TimeS") >= '0'::bigint)"
"  ->  Sort  (cost=12319742.87..12438008.64 rows=47306308 width=93)"
" Sort Key: t1."TaskIndex", t1."JobID""
"  ->  Seq Scan on scheduled_tasks_table t1  (cost=0.00..1438498.08 rows=47306308 width=93)"
"  ->  Materialize  (cost=7662858.23..7874671.39 rows=42362632 width=28)"
6
  • 3
    A properly formatted output of explain (analyze, verbose) would be more helpful. And make sure you keep the indention in the plan - that is important. Also show us the definition (=create index) of the indexes. Did you try a single index on ("TaskIndex", "JobID", "TimeS")? Are the columns all of the exact same data type in both tables?
    – user1822
    Jan 31, 2017 at 6:27
  • Actually, EXPLAIN ANALYSE is also too slow for me. I have a single index on ("TaskIndex", "JobID", "TimeS"), the definition: ` CREATE INDEX "dead_tasks_table_TimeS_JobID_TaskIndex_idx" ON public.dead_tasks_table USING btree ("TimeS", "JobID", "TaskIndex") `'
    – Masoumeh
    Jan 31, 2017 at 22:51
  • Which are the sizes (row count) of scheduled_tasks_table and dead_tasks_table and how much time is "really slow"?
    – joanolo
    Jan 31, 2017 at 23:15
  • scheduled_tasks_table number of rows: 97,200,000 and dead_tasks_table number of rows: 48,800,000 , the slowness is like: It has been running for 2 days and yet no result!
    – Masoumeh
    Jan 31, 2017 at 23:33
  • Do you still need help with query? Feb 4, 2017 at 17:36

1 Answer 1

2

Probably problem is here:

(t2."TimeS"-t1."TimeS")>='0'

Postgresql does not use index for this. Can you try just:

t2."TimeS" >= t1."TimeS"
5
  • Thanks Roman, but I don't think so because even when I remov this condition, the query is still slow.
    – Masoumeh
    Jan 31, 2017 at 22:57
  • When you remove this conditions, as you broaden your search, you might probably get many more rows, which would increase the timing. So, if feasible, try to test as suggested and evaluate the result...
    – joanolo
    Jan 31, 2017 at 23:10
  • I did and the EXPLAIN didn't show many differences.
    – Masoumeh
    Jan 31, 2017 at 23:43
  • How many rows in t2 do you need for each row t1 ? If 1 - you must change your query -- if not : rows=20,251,904,912 . Is too many as for me. Jan 31, 2017 at 23:50
  • As mentioned by joanolo you must narrow result of query. Two way - the first do you have many records in t2 for each t1 record? If yes - do you want in result to see all this records or just one last / first? The second way - do you need this big result table or probably you need some stats? Feb 1, 2017 at 15:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.