search

FirebirdSQL / firebird

Firebird server, client and tools
https://www.firebirdsql.org/
1.24k stars 212 forks source link

RDB$CONFIG does not have indicies #7621

Open EPluribusUnum opened 1 year ago

EPluribusUnum commented 1 year ago

Hi *!

e.g.:

select * from rdb$config c where c.rdb$config_name = 'ReadConsistency'

does a full scan

aafemt commented 1 year ago

This tiny pseudo-table that is kept in memory has no advantage from indices.

EPluribusUnum commented 1 year ago

"Full Scan" reported for this query in plan. This triggers WARN in our checking tool. Could you report in this case something else, e.g. "Full Memory Scan"? BTW MON$ tables also are in memory but expesive to read, because of on-the-fly generation. RDB$CONFIG is created for the first connection only?

aafemt commented 1 year ago

Firebird configuration file is loaded into memory once at start. Databases configuration file is loaded at attachment if has been changed. Configuration items provided in DPB block are... provided in DPB block.

If your tool is nervous about "Full Table Scan" it is not a Firebird problem. Full table scan is an optimal strategy in some cases not limited to pseudo-tables.

hvlad commented 1 year ago

Firebird configuration file is loaded into memory once at start. Databases configuration file is loaded at attachment if has been changed. Configuration items provided in DPB block are... provided in DPB block.

Just want to add that RDB$CONFIG is a view of configuration data above and such a view is created\destroyed on demand, i.e. when referenced in some query.

As for describing operations on virtual tables using some (distinct) markers in explained plan - I tend to support this. @dyemanov, what do you think ?

dyemanov commented 1 year ago

I don't mind, let's just decide how it should be named. "Full virtual table scan" or just "Virtual table scan" or maybe something like "In-memory table scan"?

Regardless, the checking tool must be fixed, "full table scan" will appear much more often in v5 but the performance is expected to improve, so warnings will be useless.

aafemt commented 1 year ago

I would suggest to borrow Oracle's "COLLECTION ITERATOR".

AlexPeshkoff commented 1 year ago

On 6/7/23 16:16, Dmitry Yemanov wrote:

"Virtual table scan" is OK for me.

EPluribusUnum commented 1 year ago

Technically I get WARN not because of "Full Scan" but because of it's not "Unique Scan", and the SQL does not have "ORDER BY" part. It's time to have constraint without index?

sim1984 commented 1 year ago

What is the constraint? Why is it here? This table is not populated by the user. Your plan analyzer is no good. Just look at how the tools for other DBMS work. In them, full scan is not a sign of something bad.