Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > pgsql.performance > #1

Slow query on partition table

Show all headers | View raw

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?

Back to pgsql.performance | Next — Next in thread | Find similar

Thread

Slow query on partition table rtfrodrigo@gmail.com - 2016-01-22 07:56 -0800
  Re: Slow query on partition table rtfrodrigo@gmail.com - 2016-01-22 08:02 -0800

csiph-web