gonzono
gonzono

Reputation: 1

Inconsistent query result when joining with hypertable

In Postgresql this query returns sometimes rows, sometimes no rows. Data in tables not changing between runs.

Table steam."TopWishlistHistory" is Hypertable (Timescale extension)

Query:

SELECT thw."Added" FROM steam."TopWishlistHistory" thw
LEFT JOIN steam."GameCompany" gc ON gc."SteamGameId" = thw."SteamGameId" 
WHERE thw."Added" = (SELECT MAX(aa."Added") FROM steam."TopWishlistHistory" aa)

Tables schemas:

CREATE TABLE IF NOT EXISTS steam."TopWishlistHistory"
(
    "Id" integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "SteamGameId" integer NOT NULL,
    "Position" smallint,
    "Added" timestamp without time zone NOT NULL,
    CONSTRAINT steamgame_id_fk FOREIGN KEY ("SteamGameId")
        REFERENCES steam."Games" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
CREATE TABLE IF NOT EXISTS steam."GameCompany"
(
    "Id" integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "SteamGameId" integer NOT NULL,
    "CompanyId" integer NOT NULL,
    "Type" integer NOT NULL,
    "Added" timestamp(6) without time zone,
    CONSTRAINT "GameCompany_pkey" PRIMARY KEY ("Id"),
    CONSTRAINT sdfadsfdgddf FOREIGN KEY ("CompanyId")
        REFERENCES dbo."Company" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT sdfsfdgddf FOREIGN KEY ("SteamGameId")
        REFERENCES steam."Games" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

Everything works ok with cte:

with t as (SELECT * FROM steam."TopWishlistHistory")     
SELECT t."Added" FROM t      
JOIN steam."GameCompany" gc ON gc."SteamGameId" = t."SteamGameId"  
WHERE t."Added" = (SELECT MAX("Added") FROM t)

Upvotes: 0

Views: 66

Answers (1)

morphineglelly
morphineglelly

Reputation: 177

I would use this tool here: https://explain.dalibo.com/ to understand how the different queries (traditional and CTE) work, especially in the planning phase (before the execution).

Since I had some tricky issues in timescaleDB with the planning part of the query (see also this Blog Post of the official TimescaleDB website), I believe it can be beneficial to investigate on that part.

To do that: use EXPLAIN ANALYZE before the text of the query before the actual text of the query, then paste the result in the web tool.

Some additional thing that I would look into:

  1. is Compression enabled? is data being constantly added between queries (you said no but I would double check it)

  2. investigate more on the subquery SELECT MAX(aa."Added") FROM steam."TopWishlistHistory" aa and the result it returns over time.

  3. you are using two different aliases for TopWishlistHistory: aa and thw. Try removing all aliases.

I apologize if I cannot provide a straight solution, I hope this answer helps you.

Upvotes: 0

Related Questions