Weird tables with numbers and underscores in PostgreSQL execution plans

Question

I'm debugging performance problems with my DB and see some unusual entries in the DB log:

2021年04月21日 01:07:33.857 UTC [14823] user@db LOG: duration: 933645.469 ms plan:
Query Text: select systemv0_.id as id1_11_0_, systemv0_.createdTs as createdT2_11_0_, systemv0_.systemType as syste3_11_0_, systemv0_.foundProblemsCount as foundPro4_11_0_, systemv0_.groupid as groupid8_11_0_,... (rest omitted for brevity)

What are these strange identifiers with numbers and underscores? Normally an execution plans has clear names and identifiers such as systemview.id.

My hypothesis is that there are several concurrent queries and the performance issues are due to the DB desperately trying to remain consistent under low memory conditions. I noticed a couple suspicious things in other queries - several Recheck Cond and Materialize tree nodes, which appear to be often happening due to insufficient size of some DB buffers (caused by low host memory?). It would be nice if someone could confirm or debunk my theory.

Answer 1

select systemv0_.id as id1_11_0_
, systemv0_.createdTs as createdT2_11_0_
, systemv0_.systemType as syste3_11_0_
, systemv0_.foundProblemsCount as foundPro4_11_0_
, systemv0_.groupid as groupid8_11_0_
,...

"systemv0_" might be a table name (but I suspect that's unlikely).
"systemv0_" might be a Correlation Name (or "alias") for real table (as in select ... from table1 as systemv0_).

This kind of "weird-looking aliasing" is very common with Object-Relational-Mapping (ORM) tools, which I suspect is what generating this query. Rest assured, there is nothing "nasty" happening inside your database.

Sure, this query looks odd to you and me but, to your DBMS, it's no different from the bread-and-butter "select * from table1" style query that we would write by hand. (Not that you'd use "select *" in application code, of course).

Answer 2

Those are not the table names, but aliases. You can use them to make a query more readable:

SELECT some.col1, another.col2
FROM some_really_long_table_name AS some
 JOIN another_terrible_table_name AS another
 USING (col3)
WHERE some.col4 = 42;

But of course you can also abuse aliases to obfuscate and inflate a query, as some object-relational mappers do.