r***@gmail.com
2016-01-22 15:56:40 UTC
Hi, I have a problem with a query.
Let's consider a table with columns (id, timestamp, col, ....others....)
This is a partitioned table that uses the timestamp for the partition.
This table has an index in (timestamp, id), and another index in (col).
The problem happens in a query where I use a WHERE with both the timestamp and col.
In fact I do not need the timestamp, I am just using it for the partition to optimize the select of the table.
This query is very slow, but if I only use col as WHERE condition directly in the table it is really fast.
Probably an index with (timestamp, col) would fix but that will use a lot of space (like half the table size), can I fix this any other way?
Table structure:
http://pastebin.com/CPyFQK9q
Explain Analyse:
http://explain.depesz.com/s/xfRd
Any ideas how to improve this?
Let's consider a table with columns (id, timestamp, col, ....others....)
This is a partitioned table that uses the timestamp for the partition.
This table has an index in (timestamp, id), and another index in (col).
The problem happens in a query where I use a WHERE with both the timestamp and col.
In fact I do not need the timestamp, I am just using it for the partition to optimize the select of the table.
This query is very slow, but if I only use col as WHERE condition directly in the table it is really fast.
Probably an index with (timestamp, col) would fix but that will use a lot of space (like half the table size), can I fix this any other way?
Table structure:
http://pastebin.com/CPyFQK9q
Explain Analyse:
http://explain.depesz.com/s/xfRd
Any ideas how to improve this?