PostgreSQL - Query optimization on SUM and LEFT JOIN

Question

I'm taking the month using to_char() which is common in two tables namely Table1 and Table2.

But in Table2 I have both date and month.

Also I've created a index on Table1. If I write the same query only Table1 I get the results within 1000ms.

I'm taking sum of values and combining the results using left join.

Here's the query.

SELECT
    "month", -- Table2
    sum("value1"), -- Table2
    sum("value2") -- Table1
FROM Table2
LEFT JOIN Table1 ON
    Table2."month" = to_char(Table1.Date, 'Mon-yy')
WHERE Table2.Date BEtween '2014-01-01' AND '2014-03-01'
GROUP BY "month"

EXPLAIN OF THE Query :

"GroupAggregate  (cost=88133.61..3688425.12 rows=2 width=15)"
"  ->  Merge Left Join  (cost=88133.61..2707006.48 rows=130855816 width=15)"
"        Merge Cond: (Table2.month = (to_char((Table1.date)::timestamp with time zone, 'Mon-yy'::text)))"
"        ->  Sort  (cost=8922.32..9056.34 rows=53609 width=11)"
"              Sort Key: Table2.month"
"              ->  Seq Scan on Table2(cost=0.00..3885.28 rows=53609 width=11)"
"                    Filter: ((date >= '2014-01-01'::date) AND (date   Materialize  (cost=79211.29..81652.22 rows=488186 width=8)"
"              ->  Sort  (cost=79211.29..80431.75 rows=488186 width=8)"
"                    Sort Key: (to_char((Table1.date)::timestamp with time zone, 'Mon-yy'::text))"
"                    ->  Seq Scan on Table1(cost=0.00..19735.86 rows=488186 width=8)"

Currently I have about 500k rows in Table1.

Everyday I update the table with around 3 to 4k records to Table1.

The query just keeps running.

I get no results.

Can i anyone say me where I'm going wrong?

Can you add your table definition and an explain of the query? — Wolph
I'm a newbie. Managed to get the EXPLAIN of the query. How will I get the table definition? — Unknown User
@a_horse_with_no_name - When I run this EXPLAIN (ANALYZE, BUFFERS). Query doesn't stop at all. It keeps on running for about 5 mins. — Unknown User
Which indexes are defined on the tables? Do you have an index on table2.date? How many rows from table2 will be selected by the condition between '2014-01-01' AND '2014-03-01'? And why do you have a month column in table2 if you already have a date column that apparently stores the full date. — a_horse_with_no_name

Lucas Lucas · Accepted Answer · 2014-04-10T16:08:03

For your query to run 'efficiently', you will need 2 indexes. Judging by the explain, table2 needs an index on the date column.

CREATE INDEX ON table2 using btree(date);

table1 will need a computed index with the month name and day number e.g. "Apr-14".

CREATE INDEX ON table1 using btree(to_char(date, 'Mon-yy'));

[edit, as a_horse_with_no_name said this will not work because to_char is not immutable - you could get round this by writing your own immutable function, but really it would be much simpler if you just stored a date column!]

That said, text matching is slower than dates or numbers and if you want to order the information you will only get an alphabetical sort.

PostgreSQL - Query optimization on SUM and LEFT JOIN

1 Answers