How to speed up count()?

[Mulugruntz]

The schema:

type SubTable {
        required property name -> str {
            constraint exclusive;
        }
    }

scalar type ufloat64 extending float64 {
        constraint min_value(0);
    }

type History {
        required link sub -> SubTable;
        required property timestamp -> datetime;
        required property value -> ufloat64;

        index on (.timestamp);
    }

The query:

root@6c3c8143c4a9:/# time edgedb query "select 1+1;"
2

real    0m0.292s
user    0m0.015s
sys     0m0.027s
root@6c3c8143c4a9:/# time edgedb query "select count(History);"
27640978

real    1m25.543s
user    0m0.016s
sys     0m0.032s

We can see that the count takes 85 seconds to complete for 27 million items. I find this pretty slow for an information without any filter. I would have imagined this to be O(1) (but it clearly isn't).

Is it expected? Any suggestion?

[elprans]

This is a known characteristic of Postgres and is due to how it manages row visibility. See this Wiki page for some background. There's no good answer here, unfortunately.

[Mulugruntz]

Hmmm rings a bell. I remember that estimate count from my Oracle days.
Do we have a similar feature in EdgeDB? (the estimate)

[elprans]

Do we have a similar feature in EdgeDB? (the estimate)

Not currently, but a whole-table variant shouldn't be hard to add. Something like

WITH HistoryType := (SELECT schema::ObjectType FILTER .name = 'default::History')
SELECT schema::get_approx_count(HistoryType);

Getting estimates for filtered queries require gnarly hacks that extract information from PostgreSQL's EXPLAIN, so those are unlikely to be added.

[Mulugruntz]

sure, that would be nice.
Also having a Pg EXPLAIN / Oracle EXPLAIN PLAN some time in the future would be nice to understand better the plan made by EdgeDB and improve our queries / schemas.

[clmnin]

At this point EXPLAIN is "the feature" to nudge a team to start using EdgeDB