Why only one data chunk is scanned when querying a table, but many data chunks are scanned when querying a view?

[bodypumper]

Hi Relevant system information:

• OS: [Ubuntu 11.13-1.pgdg18.04+1]
• PostgreSQL version [PostgreSQL 11.13]
• TimescaleDB version [1.7.5]
• Installation method: [apt install]

Preconditions 1：I created a table and a hypertable before. SELECT create_hypertable('sys_device_delivery_t', 'upload_time', chunk_time_interval => INTERVAL '7 days')

2：I created a unique index on table create unique index sys_device_delivery_ts_idx on test (entity_id asc, key asc, ts asc, upload_time desc);

3：I created a simple query view on table.

Explain analyze result 1：query table

2：query view

[nikkhils]

@bodypumper will it be possible for you to share the schema of the involved table[s] and the views and queries in text form here?

[bodypumper]

@bodypumper will it be possible for you to share the schema of the involved table[s] and the views and queries in text form here?

@nikkhils Hi,

the schema of table：sys_device_delivery_t

CREATE TABLE sys_device_delivery_t (
	entity_id varchar(40) NOT NULL,
	"key" varchar(255) NOT NULL,
	ts int8 NOT NULL,
	upload_time timestamptz NOT NULL,
	bool_v bool NULL,
	str_v varchar(10000000) NULL,
	long_v int8 NULL,
	dbl_v numeric(15,2) NULL,
	data_type varchar(10) NULL,
	device_sn varchar(40) NULL,
	cumulative bool NULL,
	set_flag int2 NULL
);
CREATE UNIQUE INDEX sys_device_delivery_ts_idx ON public.sys_device_delivery_t USING btree (entity_id, key, ts, upload_time DESC);

-- Table Triggers

create trigger ts_cagg_invalidation_trigger after
insert
    or
delete
    or
update
    on
    public.sys_device_delivery_t for each row execute procedure _timescaledb_internal.continuous_agg_invalidation_trigger('1');
create trigger ts_insert_blocker before
insert
    on
    public.sys_device_delivery_t for each row execute procedure _timescaledb_internal.insert_blocker();

the schema of view：delata_summary_v_1

CREATE VIEW delata_summary_v_1 AS
SELECT cur.entity_id,
            cur.key,
            cur.upload_time,
            cur.dbl_v AS upload_value,
            cur.ts,
            cur.dbl_v - lag(cur.dbl_v) OVER (PARTITION BY cur.entity_id, cur.key ORDER BY cur.entity_id, cur.key, cur.upload_time) AS diff,
            cur.set_flag
           FROM sys_device_delivery_t cur

[NunoFilipeSantos]

Hi @bodypumper! Can you please update to TiemscaleDB 2.5.0 with a supported PG version (12, 13, 14) and get back to us? Thank you?

[bodypumper]

Hi @bodypumper! Can you please update to TiemscaleDB 2.5.0 with a supported PG version (12, 13, 14) and get back to us? Thank you?

Hi @NunoFilipeSantos ! I pulled the images of timescaledb,But it did't work. here it is: timescale/timescaledb:2.5.0-pg12; timescale/timescaledb:2.5.0-pg13; timescale/timescaledb:2.5.0-pg14

Thank your advice !

[nikkhils]

@bodypumper do you mean you got the same results as you got with 1.x earlier with 2.5 as well?

[bodypumper]

@bodypumper do you mean you got the same results as you got with 1.x earlier with 2.5 as well?

@nikkhils yes, i got the same results.

[gayyappan]

The most likely cause is that the where clause does not get pushed down into the view. We don't handle this case. This would be an enhancement.

[gayyappan]

This is plan on PG14 post 2.5. on master plan.txt

[bodypumper]

@gayyappan Hi, Will this problem be dealt with later?

[leeweit]

Hi,same problem to me , how to fix it.

[bodypumper]

Hi,same problem to me , how to fix it.

Hi, i query tables instead of views. I hope the government can fix this problem.

[bodypumper]

pg: 15 tsdb: 2.10.1 data count: 1.4 million Table or view scan blocks have the same results

view query explain SQL

table query explain SQL