Add pool_id and an index by the pool_id and height fields in block table

[Antoninich]

Now, in order to find out which block has which pool, have to query two tables block and reward_recip_assign. If you create a query to the block table and annotate recip_id from the reward_recip_assign table, then filtering by the annotated field is very slow, because it is not indexed.
This is required for the Pool section (https://explorer.it24.pw/pools/ or https://explorer.notallmine.net/pools/).

And make indexing by the pool_id and height fields with reverse sorting. For example, CREATE INDEX block_pool_id_height_idxONblock (pool_id, height DESC);

[ohager]

Mhh... After looking into this request, I have some doubts:

1. If I do select * from "reward_recip_assign" where "recip_id" = -7391387264657651612 then I'd get all mined blocks by a given pool (here: NAMpool)
2. If I do select "recip_id" from "reward_recip_assign" where "height" = 1226548; I get the recipients Id aka pool Id
3. The pool lists on the explorer are pretty fast.

So, it's not really clear what/why we need here/it

[Antoninich]

2. select "recip_id" from "reward_recip_assign" where "height" = 1226548;

This query does not show which recip_id forged the block. For example, select * from reward_recip_assign where height=1227405; will not show anything.

[ohager]

1227405

Of course, because it's not a pool miner, but solo miner.

It's still not clear to me what you want to achieve. Don't be offended, I just don't understand the feature request.

What is the query you want to run?

[Antoninich]

Query to get recip_id and its last forged block:

SELECT (
        SELECT U0.`recip_id`
          FROM `reward_recip_assign` U0
         WHERE (U0.`height` <= (`block`.`height`) AND U0.`account_id` = (`block`.`generator_id`))
         ORDER BY U0.`height` DESC
         LIMIT 1
       ) AS `pool_id`,
       MAX(`block`.`height`) AS `block`
  FROM `block`
 WHERE (NOT ((SELECT U0.`recip_id` FROM `reward_recip_assign` U0 WHERE (U0.`height` <= (`block`.`height`) AND U0.`account_id` = (`block`.`generator_id`)) ORDER BY U0.`height` DESC LIMIT 1) IS NULL) AND NOT ((SELECT U0.`recip_id` FROM `reward_recip_assign` U0 WHERE (U0.`height` <= (`block`.`height`) AND U0.`account_id` = (`block`.`generator_id`)) ORDER BY U0.`height` DESC LIMIT 1) = (`block`.`generator_id`)))
 GROUP BY 1
 ORDER BY 2 DESC

In Django, this query looks like this:

queryset = (
    Block.objects.using("java_wallet")
    .annotate(
        pool_id=RewardRecipAssign.objects.using("java_wallet")
        .filter(height__lte=OuterRef("height"))
        .filter(account_id=OuterRef("generator_id")))
        .order_by("-height")
        .values("recip_id")
        [:1]
    )
    .exclude(pool_id__isnull=True)
    .filter(~Q(pool_id=F("generator_id"))))
    .values("pool_id")
    .annotate(block=Max("height")))
)

What this query does (conditionally):

1. A query is run for each row in the Block table, for example:
   SELECT recip_id FROM reward_recip_assign WHERE (height <= 1228277 AND account_id = -3605062450941468481) ORDER BY height DESC LIMIT 1;
   Where 1228277 is the height of the Block table,
   -3605062450941468481 is the generator_id from the Block table
2. As a result of executing queries for each row of the Block table, we get a table with the fields generator_id, height, recip_id
3. Group recip_id by maximum height value, i.e. we get a unique recip_id and its maximum height.

Such a query is slow, and the simplest solution is to add the recip_id field to the Block table (https://stackoverflow.com/questions/45437096/django-filter-by-annotated-field-is-too-slow)
I hope you know what I mean. If something is not clear, I am ready to answer your questions.

[ohager]

Thanks for detailed explanations...

[damccull]

If this doesn't affect the running of the node itself, I vote against it. Another index will increase the database size. Maybe not significantly at first but we want to ensure it grows as slowly as possible to ensure long term viability.

This is something that could be offloaded into the explorer database. Run a one time query on the node and store the results in the explorer database where you can index however you like. That'll get you your desired speed while maintaining the node database without an extra index.

I realize this may be inconvenient but using the explorer database is really not a difficult solution.

Additionally, the duplicate data means we have now lowered the normalization level, ensuring we have an opportunity to have conflicting data. If you use a calculated field, you avoid the duplicate data but you don't gain the speed benefit you desire.

[Antoninich]

This is something that could be offloaded into the explorer database. Run a one time query on the node and store the results in the explorer database where you can index however you like. That'll get you your desired speed while maintaining the node database without an extra index.

Your proposed implementation is working on my test explorer now. And this is the most optimal solution so far.
But I have one more idea, which I need to think more carefully.

[ohager]

can I close this? @Antoninich