Ibis adding 'AT TIME ZONE' in PostgreSQL backend

[WR-Long]

Hi, I found Ibis recently and am very impressed with the concept. I am not a data engineer or data scientist but I must occasionally do some time-series analysis for my job. For small projects I can work with Pandas data frames but to scale up I must access a corporate "data system". This "data system" has ranged from a RDBMS (of which we use at least 2), to a data warehouse, to a cloud data lake, to a cloud data-as-a-service all within the past few years. The idea that I could develop an analysis and then only must change the connection string has tremendous potential.

I have been testing with a PostgreSQL database and am unable to get Ibis expressions to match the query performance of an SQL query using subqueries.

My data set consists of system metrics. The metrics are indexed and partitioned on time and indexed on host. I would like to determine the most recent uptime for hosts that have been on within the past day. Example data:

	time	host	load1	load15	load5	n_cpus	n_unique_users	n_users	uptime	uptime_format
0	2024-01-07 10:01:10+00:00	hostA	0.03	0	0.04	4	0	0	nan
1	2024-01-07 10:01:10+00:00	hostA	nan	nan	nan	nan	nan	nan	1.7928e+06
2	2024-01-07 10:01:10+00:00	hostA	nan	nan	nan	nan	nan	nan	nan	20 days, 18:00
3	2024-01-07 10:01:10+00:00	hostB	nan	nan	nan	nan	nan	nan	949167
4	2024-01-07 10:01:10+00:00	hostB	nan	nan	nan	nan	nan	nan	nan	10 days, 23:39
5	2024-01-07 10:01:10+00:00	hostB	0.01	0.24	0.17	8	0	0	nan

Due to my partitions and index, I write subqueries to first filter to the last day, then rank by host to determine the most recent measurement, and then finally drop rows that are NULL. The Ibis expression I have been using:

t0 = (system
     .filter(system['time'] >= (ibis.now() - ibis.interval(days=1)))
     .group_by('host')
     .order_by(ibis.desc('time'))
     .mutate(rank=ibis.rank())
     )

t1 = (t0.filter(t0['rank']==0)
     )

t2 = (t1.dropna('uptime_format')
     .select(['host','uptime_format','time'])
     .order_by(ibis.desc('host'))
     )

t2.execute()

The output:

	host	uptime_format	time
0	hostB	10 days, 23:40	2024-01-07 10:01:50+00:00
1	hostA	20 days, 18:00	2024-01-07 10:01:50+00:00

Generating an SQL statement with ibis.to_sql() and performing EXPLAIN ANALYZE on both, Ibis has a much higher cost. I believe this is because Ibis is adding AT TIME ZONE ‘UTC’ in my WHERE statements resulting from the .filter(). The EXPLAIN ANALYZE shows I am doing full table scans instead of hitting indices.

I have also tried: .filter(system['time'] >= ibis.timestamp(ibis.now(), timezone='UTC') - ibis.interval(days=1)). with the same result.

My system.schema():

ibis.Schema {
  time            !timestamp('UTC')
  host            string
  load1           float64
  load15          float64
  load5           float64
  n_cpus          int64
  n_unique_users  int64
  n_users         int64
  uptime          decimal
  uptime_format   string
}

How can I get Ibis to NOT include the ‘AT TIME ZONE’ or is there another method to improve performance and get the query to hit my index instead of doing a full table scan? Thanks!

[cpcloud]

@WR-Long Thanks for opening a discussion!

I fixed an issue related to AT TIME ZONE yesterday (#7942), it turned that removing AT TIME ZONE was the fix for that issue.

Happy to help poke around query performance a bit.

It would be very helpful if you could provide the query you're comparing against as well as some DDL that sets up the indexes you're referring to.

[WR-Long]

@cpcloud Thanks for the quick response!

Query I was comparing to:

SELECT T1.HOST,
	T1.UPTIME_FORMAT,
	T1.TIME
FROM
	(SELECT T0.*,
	 	RANK() OVER (PARTITION BY T0.HOST ORDER BY (T0.TIME) DESC) HRANK
	FROM
	 	(SELECT *
		 FROM PUBLIC.SYSTEM
		 WHERE TIME >= CURRENT_TIMESTAMP - INTERVAL '1 DAY'
		) T0
	) T1
WHERE HRANK = 1
AND T1.UPTIME_FORMAT IS NOT NULL
ORDER BY HOST

DDL: (I think... I am loading metrics with Telegraf and I believe it created the tables initially. The partitioning is handled by TimescaleDB. So I just nabbed the CREATE Script from pgAdmin.)

-- Table: public.system

-- DROP TABLE IF EXISTS public.system;

CREATE TABLE IF NOT EXISTS public.system
(
    "time" timestamp with time zone NOT NULL,
    host text COLLATE pg_catalog."default",
    load1 double precision,
    load15 double precision,
    load5 double precision,
    n_cpus bigint,
    n_unique_users bigint,
    n_users bigint,
    uptime numeric,
    uptime_format text COLLATE pg_catalog."default"
)

TABLESPACE srv;

ALTER TABLE IF EXISTS public.system
    OWNER to postgres;

COMMENT ON COLUMN public.system.host
    IS 'tag';
-- Index: system_host_time_idx

-- DROP INDEX IF EXISTS public.system_host_time_idx;

CREATE INDEX IF NOT EXISTS system_host_time_idx
    ON public.system USING btree
    (host COLLATE pg_catalog."default" ASC NULLS LAST, "time" DESC NULLS FIRST)
    TABLESPACE srv;
-- Index: system_time_idx

-- DROP INDEX IF EXISTS public.system_time_idx;

CREATE INDEX IF NOT EXISTS system_time_idx
    ON public.system USING btree
    ("time" DESC NULLS FIRST)
    TABLESPACE srv;

-- Trigger: ts_insert_blocker

-- DROP TRIGGER IF EXISTS ts_insert_blocker ON public.system;

CREATE OR REPLACE TRIGGER ts_insert_blocker
    BEFORE INSERT
    ON public.system
    FOR EACH ROW
    EXECUTE FUNCTION _timescaledb_functions.insert_blocker();

I originally thought it was the query layout. Just using ibis.to_sql() and only removing AT TIME ZONE 'UTC' with no other changes was a huge improvement. Changes to starting with an Index Scan from a Seq Scan on every partition/chunk.

[cpcloud]

Nice! After removing the AT TIME ZONE, is there still a big performance difference? It sounds like there's a large improvement from doing that, I'm just wondering if that's enough to get you close to the query you're comparing against.

[WR-Long]

No real performance difference remains! (Just saw I had one sort different.) Removing AT TIME ZONE is all that is needed for testing the raw SQL for my current use case. I think the Postgres Planner/Optimizer handles the rest but I am admittedly in way over my head already at this point. William Long

[cpcloud]

Given that we're no longer generating AT TIME ZONE anymore (it'll be in the next release 8.0.0), I'm going to mark this discussion as answered!

Thanks again for bringing it up!