0

I have two tables students with 6k records and transactions with 16M records.

I have a query similar to this:

select section, count(s.students) from students as s
where upper(name) in 
    (select upper(name) from transactions as t 
        where (length(transaction_id) < 43 or 
            lower(transaction_name) = 'NA' or 
            lower(transaction_type) = 'food')
            and upper(name) not in ('JOHN DOE')) 
group by section 
order by section asc

The query plan for this is similar to below:

 GroupAggregate  (cost=2828274.40..2828291.32 rows=6 width=12) (actual time=101295.078..101301.568 rows=6 loops=1)
   Group Key: section
   ->  Sort  (cost=2828274.40..2828280.02 rows=2247 width=25) (actual time=101294.727..101300.274 rows=2414 loops=1)
         Sort Key: section
         Sort Method: quicksort  Memory: 285kB
         ->  Hash Join  (cost=2827697.15..2828149.32 rows=2247 width=25) (actual time=101278.618..101292.861 rows=2414 loops=1)
               Hash Cond: ((s.name)::text = (t.name)::text)
               ->  Seq Scan on students s  (cost=0.00..412.16 rows=5716 width=25) (actual time=0.007..2.887 rows=5720 loops=1)
               ->  Hash  (cost=2827669.07..2827669.07 rows=2247 width=21) (actual time=101278.578..101278.578 rows=2445 loops=1)
                     Buckets: 4096  Batches: 1  Memory Usage: 159kB
                     ->  HashAggregate  (cost=2827646.60..2827669.07 rows=2247 width=21) (actual time=101274.023..101276.849 rows=2445 loops=1)
                           Group Key: (t.name)::text
                           ->  Gather  (cost=1000.00..2804729.14 rows=9166982 width=21) (actual time=97.170..100735.029 rows=449343 loops=1)
                                 Workers Planned: 2
                                 Workers Launched: 2
                                 ->  Parallel Seq Scan on transactions t  (cost=0.00..1887030.94 rows=3819576 width=21) (actual time=130.428..100660.838 rows=149781 loops=3)
                                       Filter: (((name)::text <> 'JOHN DOE'::text) AND ((length((transaction_id)::text) < 23) OR (lower((transaction_type)::text) = 'food'::text)) OR OR (lower((transaction_name)::text) = 'NA'::text))))
                                       Rows Removed by Filter: 4897554
 Planning time: 10.027 ms
 Execution time: 101301.982 ms
3
  • How can lower(transaction_name) = 'NA' - ever be true? Should that be = 'na'? – Vérace Feb 6 '20 at 5:58
  • If you run this query repeatedly, which parameters are going to change between runs? And if you don't run repeatedly, why isn't 101 seconds fast enough for a one-time thing? Also, it looks you manually anonymized you EXPLAIN output, and kind of mangled it. – jjanes Feb 6 '20 at 18:42
  • Please turn on track_io_timing, and do EXPLAIN (ANALYZE, BUFFERS). – jjanes Feb 6 '20 at 18:43
0

All your time is spent in a sequential scan that discards almost all the rows it scans. Unfortunately you have an OR in there, so the best you can do is to create three indexes and hope for a "bitmap or" of the three index scans:

CREATE INDEX transactions_type_idx ON transactions (lower(transaction_type::text));
CREATE INDEX transactions_name_idx ON transactions (lower(transaction_name::text));
CREATE INDEX transactions_id_length_idx ON transactions (length(transaction_id::text));

Your Answer

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

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