Query stops using index after a UNION ALL

[fetchezar]

Relevant system information:

• OS: Debian 10.3 64 bit
• PostgreSQL version: 11.7
• TimescaleDB version: 1.7.2
• Installation method: apt install

Describe the bug I'll start with the example tables, these two are the hypertables with the historic samples:

x	id	tstamp	value	quality
value_int32	int8	timestampz	int4	boolean
value_float	int8	timestampz	float4	boolean

With these keys on both:

• pkey -> (id, tstamp)
• fkey -> value_xyz.id to signal.id

This normal table is the registry of all the signals we're storing: x	id	data_type	name
signal	int8	int2	text

• pkey -> id
• unique -> name

Very simple right? You want float values you query the float table, you want integer values and you query the integer table. If you want bit flags for instance, you'd use the integer table too so your bits don't get garbled by the IEEE float representation.

In this example, if you were to say, want to query a few alert counters you know the name of, you'd issue this query:

SELECT v.*
FROM value_int32 v
JOIN signal s ON s.id = v.id
WHERE s.name LIKE '%_alert_counter'
ORDER BY v.tstamp DESC
LIMIT 10

This kind of query works fine and runs in less than 100ms, you can see from the explain result that it uses indices and all is well: explain_simple_query.txt

Now say that you don't really know the specific data type of the signals you're querying, or that you don't care about bit flags and just want some values you know they're well within what a float4 can handle. To make the queries easier you could create this view:

CREATE OR REPLACE VIEW value_numeric
AS SELECT value_int32.id,
    value_int32.tstamp,
    value_int32.value,
    value_int32.quality
   FROM value_int32
UNION ALL
 SELECT value_float.id,
    value_float.tstamp,
    value_float.value,
    value_float.quality
   FROM value_float;

And now, we try to issue the previous query against this view:

SELECT v.*
FROM value_numeric v
JOIN signal s ON s.id = v.id
WHERE s.name LIKE '%_alert_counter'
ORDER BY v.tstamp DESC
LIMIT 10

This query takes two minutes, look at the explain: explain_view_query.txt

It goes from using the indices of each chunk as expected, to sequentially iterate over all the chunks avoiding using the index at all. As you can imagine, this is totally awful for performance in my very simple schema.

Expected behavior I'd expect the indices to be used in these sorts of queries, improving the performance of the query.

Actual behavior Indices don't get used in the view, thus performance becomes awful.

Additional context I'm not a DBA so honestly I'm not sure what is happening exactly by looking at this. I don't know if Timescale is at fault here or if it's a fault of PostgreSQL's planner.

EDIT: Here's a script to create the example schema with compression settings: create_example_schema.txt

[svenklemm]

The problem with the query is that the UNION makes the pushdown of the sort and limit impossible. If you manually push it down it should perform much better:

SELECT *
FROM (
  SELECT *
  FROM value_int32 vi
    JOIN signal s ON s.id = vi.id
  WHERE s.name LIKE '%_alert_counter'
  ORDER BY vi.tstamp DESC
  LIMIT 10) vi
UNION ALL
SELECT *
FROM (
  SELECT *
  FROM value_float vf
    JOIN signal s ON s.id = vf.id
  WHERE s.name LIKE '%_alert_counter'
  ORDER BY vf.tstamp DESC
  LIMIT 10) vf
ORDER BY tstamp DESC
LIMIT 10;

[fetchezar]

I see, but then I wouldn't be able to use the unifying table for ease of querying, it misses the whole point of this.

Look at this other example, let's not use a JOIN, instead let's use a sub query:

SELECT v.*
FROM value_numeric v
WHERE id IN (SELECT id FROM signal WHERE name LIKE '%_alert_counter')
ORDER BY v.tstamp DESC
LIMIT 10

The EXPLAIN looks as you'd expect, with a bunch of sequential scans over entire chunks. explain_slow_subquery.txt

But now look at this:

SELECT v.*
FROM value_numeric v
WHERE id = (SELECT id FROM signal WHERE name LIKE '%_alert_counter')
ORDER BY v.tstamp DESC
LIMIT 10

This query does use the index and it runs in milliseconds: explain_fast_subquery.txt

In all cases there is actually a single signal with the '_alert_counter' name, I'm just using a LIKE for example purposes since we'd actually want to match multiple signals by suffix.

It doesn't seems like UNION ALL is making every optimization 'impossible', since the second query uses them just fine. Why the difference in behavior?

[svenklemm]

You posted the same query twice. While UNION ALL does not prevent all optimizations it will prevent the limit pushdown since the order can no longer be derived from the index scan leading to the injection of a sort node below the limit which means its gonna process all data that match your filter, then sort it and then pick the first 10.

[fetchezar]

You posted the same query twice.

No, please look at the ID comparison:

WHERE id IN (SELECT id FROM signal WHERE name LIKE '%_alert_counter') WHERE id = (SELECT id FROM signal WHERE name LIKE '%_alert_counter')

IN operator vs = operator. In any case it should be clear by looking at the explain result that one uses the ID index and the other doesn't.

But going along what you said, I think it makes sense that in the 2nd cases it does uses the index because the order won't change, whereas in a case where there are more IDs, the order might change so it won't use the index.

If the LIMIT is the issue here as you say, swapping it for a tstamp check should go around that right? Please look carefully at the WHERE conditions:

SELECT v.*
FROM value_numeric v
WHERE id IN (SELECT id FROM signal WHERE name LIKE '%_alert_counter') 
  AND v.tstamp >= (NOW() - '1 DAY'::INTERVAL)
ORDER BY v.tstamp DESC

This case looks really weird to me, first it doesn't uses the id index whatsoever, and it uses the tstamp index in some chunks, not all of them: explain_tstamp_query.txt

SELECT v.*
FROM value_numeric v
WHERE id = (SELECT id FROM signal WHERE name LIKE '%_alert_counter') 
  AND v.tstamp >= (NOW() - '1 DAY'::INTERVAL)
ORDER BY v.tstamp DESC

Now in this case it index scans on both checks and all chunks: explain_tstamp_index_query.txt

Moreover, the difference is also pretty big: 1st case takes ~4 seconds and 2nd case takes ~80ms, for the exact same result. It isn't as big as in the previous case since the explicit tstamp check makes it skip a lot of chunks but still it's a really big difference.

You can imagine the second query isn't ideal since from an ORM, or in any server side piece of code, the 2nd case requires the user to iterate over their signals querying them one by one, whereas the 1st case it could be queried in a single step.

Even removing the ORDER BY does nothing, with a tstamp range check it should be limited to only a few specific chunks, and use the indices just fine, I don't understand why it wont use them and ruin performance like this.

[svenklemm]

Oh sorry I missed the slight difference in those queries. You could try using EXISTS instead of IN

SELECT v.*
FROM value_numeric v
WHERE EXISTS(SELECT 1 FROM signal WHERE name LIKE '%_alert_counter' AND signal.id = v.id) 
  AND v.tstamp >= (NOW() - '1 DAY'::INTERVAL)
ORDER BY v.tstamp DESC

What exactly do you want to achieve with your query? Do you want to get the last 10 values even with duplicate ids or do you want the last value for each id matching your filter?

[fetchezar]

In my original post I explained each table's purpose, also at the bottom you have a script to create the example schema with its relationships.

You could try using EXISTS instead of IN

It still won't use the index over the 'id' key, and takes a few seconds. Albeit it seems it is faster, which is a good sign.

What exactly do you want to achieve with your query? Do you want to get the last 10 values even with duplicate ids or do you want the last value for each id matching your filter?

It could be both of them, I limited this example to getting the last 10 values of a specific signal because the explain is easier to read.

My interest is in being able to do those kind of queries with LIKE expressions, where you match one or more signals with it, and query their historic data (it could be the last X values, or the last 24h of values, or some aggregate expression too like "interpolated values each 30min from the last 3 days").

The signal names are unique but also they follow patterns, without going into too much detail, they're in the form "[device][signal_name]". So you can do LIKE expressions in the form of "%[signal_name]" to get all values of a particular kind of signal from all devices, or in the form of "[device_name]%" to get all values from the signals of a particular device.

Let's look at a more "real" example. Say that you have 3 known pressure signals, you know both their names and IDs, and you want the last values of each. Since you know the IDs beforehand, you don't need to use the signal table, so you do this:

SELECT DISTINCT ON (v.id) v.*
FROM value_numeric v
WHERE id IN (123, 124, 125) 
ORDER BY v.id ASC, v.tstamp DESC

That query takes milliseconds to run. Even if the value_numeric view is an UNION ALL of like 6 tables in production. It uses both the index over id and tstamp. If you check the schema creation script you can see the historic tables are segmented by id, ordered by tstamp, it should be pretty straightforward.

Now, the more realistic example is for an user that does not know the IDs of the signals, but does knows the names, so the user would do something like this instead:

SELECT DISTINCT ON (v.id) v.*
FROM value_numeric v
WHERE id IN (SELECT id FROM signal s WHERE s.name IN ('dev1_pressure', 'dev2_pressure', 'dev3_pressure')) 
-- Or if we were going with the LIKE expressions idea, this would work the same:
-- WHERE id IN (SELECT id FROM signal s WHERE s.name LIKE 'dev%_pressure') 
ORDER BY v.id ASC, v.tstamp DESC

This takes minutes because it gives up on the id index. It shouldn't take much more time than querying the IDs separately and then using a parametrized query with the IDs inside the IN expression as parameters.

If the user happens to know in which specific table the signals reside, the user could do this instead:

SELECT DISTINCT ON (v.id) v.*
FROM value_float v
WHERE id IN (SELECT id FROM signal s WHERE s.name IN ('dev1_pressure', 'dev2_pressure', 'dev3_pressure')) 
ORDER BY v.id ASC, v.tstamp DESC

This takes milliseconds, because it does uses the id index just fine, so it isn't scanning gigabytes of compressed chunks.

In any case it seems there is no other way but to have the user have some "deeper" knowledge about the schema (ie, known beforehand in which specific table each signal resides), or have the user query the IDs separately and then use a parametrized query injecting the ID values directly. This makes it harder to use tools like Grafana since you'd need some form of scripting since you can't express it in a single query in a way that doesn't takes minutes to execute.

Have in mind this idea is coming from our "legacy" historization platform which was a specific purpose db, where we could query signals by name masks, and get results with low latency (ie, sub second). So our idea was to have a schema that would allow similar queries with good performance.

I've tested that this behavior happens even when we're using pure PostgreSQL tables, no hypertables or anything, so at this point I guess this issue has nothing to do with you guys. It's just a bit frustrating that I cant do a JOIN/sub query between a simple view and a simple table, with good performance.

[fetchezar]

I followed @svenklemm 's suggestion on slack and remade the value table in the following way:

CREATE TABLE public.sample (
    id int8 NOT NULL,
    tstamp timestamptz NOT NULL,
    vint64 int8 NULL,
    vdouble float8 NULL,
    vfloat float4 NULL,
    vbool bool NULL,
    quality bool NOT NULL,
    CONSTRAINT sample_pkey PRIMARY KEY (id, tstamp),
    CONSTRAINT sample_id_fkey FOREIGN KEY (id) REFERENCES signal(id)
);

Thus placing each signal's value in the corresponding column depending on its original data type.

Doubles and floats were separated in their own columns since I saw 40% difference in extra storage by storing 32 bit floats in float8 columns after compression. Whereas it seems we lose nothing by storing int16/32/64 data types in a single int8 column, since resulting size of the table was exactly the same after compression.

Bools could be stored in the int8 column with very little efficiency loss in storage, but I left them in their own column for integrity concerns (wouldn't want a 2 to appear on boolean signals!).

This made querying really simple by just COALESCE'ing the 4 value columns, since only one of them will have a value for each signal. In turn, since we're not depending on intermediary views with JOINs or UNION ALLs, made querying much faster and direct, since a simple JOIN with signal is processed just fine by PostgreSQL's planner and uses indexes as expected.

Thanks a lot for your time, looking forward to further development of this plugin!