postgresql – How do I debug Postgres’ join strategy?

In the process of optimizing a Postgres query, I ran into a situation where two equivalent, simple queries execute in very different amounts of time. While I can’t provide a reproducible example for this part, the queries look like:

-- Query 1, fast
select a.id
  from a
  join b on a.id = b.id
  where a.id in ('1', '2', '3')
-- Query 2, slow
select a.id
  from a
  join b on a.id = b.id
  where b.id in ('1', '2', '3')

The only difference being whether a.id or b.id is referenced in the where clause. (id is unique in table b but not in table a, if it matters.) Both tables are quite large, but the id columns are indexed.

The reason why Query 2 is running slowly is because Postgres is using a hash join. In Query 1, the planner chooses a nested loop, which is obviously much faster with such a selective condition.

Looking at the query plans (provided below) I can see that the predicted selectivities/row counts are relatively accurate–actually more accurate in the slower query. In other words, Postgres is not choosing a hash join strategy because it thinks there are more results for the condition than there are.

So, my question is: how can I debug why Postgres chose this hash join strategy? How do I find out why the query planner doesn’t consider (or overestimates) Query 1’s plan when presented with Query 2? EXPLAIN ANALYZE is great but it doesn’t allow me to see the plans that weren’t used.

Query plans:

Query 1 (fast):

QUERY PLAN                                                                                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop  (cost=0.99..1528.79 rows=344 width=17) (actual time=0.042..0.210 rows=21 loops=1)                                                                    
  ->  Index Only Scan using a_id_idx on a  (cost=0.56..19.71 rows=344 width=17) (actual time=0.027..0.049 rows=21 loops=1)
        Index Cond: (id = ANY ('{"1","2","3"}'::bpchar()))                                            
        Heap Fetches: 0                                                                                                                                           
  ->  Index Only Scan using b_pkey on b  (cost=0.43..4.39 rows=1 width=17) (actual time=0.007..0.007 rows=1 loops=21)                   
        Index Cond: (id = (b.id)::bpchar)                                                                                                 
        Heap Fetches: 0                                                                                                                                           
Planning Time: 0.136 ms                                                                                                                                           
Execution Time: 0.227 ms                                                                                                                                          

Query 2 (slow):

QUERY PLAN                                                                                                                                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather  (cost=1013.81..292692.84 rows=20 width=17) (actual time=704.293..1847.182 rows=21 loops=1)                                                                                             
  Workers Planned: 2                                                                                                                                                                           
  Workers Launched: 2                                                                                                                                                                          
  ->  Hash Join  (cost=13.81..291690.84 rows=8 width=17) (actual time=669.269..1822.987 rows=7 loops=3)                                                                                        
        Hash Cond: ((a.id)::bpchar = b.id)                                                                                                                             
        ->  Parallel Index Only Scan using a_id_idx on a  (cost=0.43..281065.62 rows=4042599 width=17) (actual time=0.019..1086.251 rows=3238563 loops=3)
              Heap Fetches: 0                                                                                                                                                                  
        ->  Hash  (cost=13.34..13.34 rows=3 width=17) (actual time=0.072..0.073 rows=3 loops=3)                                                                                                
              Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                                                                                     
              ->  Index Only Scan using b_pkey on b  (cost=0.43..13.34 rows=3 width=17) (actual time=0.040..0.068 rows=3 loops=3)                                    
                    Index Cond: (id = ANY ('{"1","2","3"}'::bpchar()))                                                                  
                    Heap Fetches: 0                                                                                                                                                            
Planning Time: 0.391 ms                                                                                                                                                                        
Execution Time: 1847.219 ms

development strategy – Yet another child theme building question: Frankenchild themes

I’m about to build a new site based on a theme close to what I’m looking for. I understand the concepts around child theming & have read the caveats about overriding php or css files like the one described here.

Using such workarounds and calls from functions.php, is it possible to build a “Frankenchild theme” by taking chunks of a second (would-be parent) theme and patching them into the child theme? I’m not quite asking about creating a child of two parents; the child would only have one “official” parent.

As an example: Start with a Gantry basic theme and add custom taxonomy search capabilities & search results formatting (say a card-based carousel). Is that stepping over the line into plugin development?

java – Architectural problem for class combination (cartesian product) for save format strategy

Hello to everyone and thank you for any suggestion.

I have a family of subclasses of Track (Track is abstract).
Every concrete Track has a different number and types of fields (attributes) that extend the basic abstract track-fields.

Then I have a family of file-types that implement some methods (saveRow() etc..).
Every file is presumed to have different type of row-formatting (imagine csv tabs, headers etc..)

ex:

  • SimpleTrack: double lat, double lon, Calendar dateTime.
  • DetectionTrack: (as SimpleTrack) + boolean detection.
  • ..
  • CsvFile
  • TxtFile

When I create a new (X)Track and a (Y) file, they are independent by nature, but.. the row is a cartesian product of track & row types.

EDIT
(to be more clear): How can I have many concrete-tracks in one hand and many FileTypes in the other hand, and create well-formatted-rows (differents for every file) by tracks which have different data (columns, headers..)?
es:

  • XtrackRow(double a, double b, Calendar date) -> to -> CVSfile (tab delimited with headers)
  • XtrackRow(double a, double b, Calendar date) -> to -> TXTfile (formatted columns and title)
  • YtrackRow(double a, string, b, int c, double e) -> to CSV ..
  • YtrackRow …. -> to -> docx file (with another kind of table or tabulation)
    ..

I see two kinds of solutions:

  1. Tracks send (right)-formatted row to file: every track has to know which kind of format apply (to rows) to give them to save to any specific type of file.
  2. Tracks send raw-data to any kind of file which is responsible to format them: in this case, the file-class must know wich kind of data has to format (every track has diffrent contents, columns, headers..). Moreover, every track-class has to send different number and types of parameter..

The second solution seems to be more fitted to the Single-Responsibility principle.. but I have no Idea how to implement it.

I tried to use Bridge Pattern to solve this problem (using first solution):

abstract class Track{
  ...
  FileInterface file;

  Track(FileInterface fileType){
    this.file = fileType;
  }

  abstract String formatConcreteTrackRow();
  
  void sendRow(){
    String rowToSave = formatConcreteTrackRow();
    file.saveRow(rowToSave);
  }
}

By this way the problem is not already solved, because every concrete-track has to implement a set of methods which returs right formatted rowString: one for every file-type.
If I use a Strategy Pattern:

class SimpleTrack extends Track{
  ...
  RowFormatStrategy rowStrategy;
  
  @override
  String formatConcreteTrackRow(){
    return this.rowStrategy.getRowString("args")
}

but in this case.. every concrete-track require a different StrategyInterface, because every concrete-track has different number and types of arguments to elaborate..
If I do not use Strategy Pattern and I define a set of methods (formatCsvRow(args),formatTxtRow(args)..) I need to include a switch(fileType) loop to choose which method to use.. breaking SOLID principles.. 🙁

Moreover..
how to impose, for every new concrete-track to have right row-format methods for every existent file-template-row?
and.. How to impose, at the same time, for every new file-class to impose new templates and relative methods in every existent concrete-track?

To be honest, it’s also quite reductive impose formatConcreteTrackRow to be a String, but it’s over and over the main problem.

I’m not interested to maintain this kind of class structure, this is only the best solution I found trying to follow SOLID principles. If you can show me a better solution, my intent is to study and understand SOLID procedures to solve these kind of purposes.

(I looked around for similar questions, but I’m not even able to define the specific problem itself..)
Thank you very much.

java – Architettural problem for class combination (cartesian product) for save format strategy

Hello to everyone and thank you for any suggestion.

I have a family of subclasses of Track (Track is abstract).
Every concrete Track has a different number and types of fields (attributes) that extend the basic abstract track-fields.

Then I have a family of file-types that implement some methods (saveRow() etc..).
Every file is presumed to have different type of row-formatting (imagine csv tabs, headers etc..)

ex:

  • SimpleTrack: double lat, double lon, Calendar dateTime.
  • DetectionTrack: (as SimpleTrack) + boolean detection.
  • ..
  • CsvFile
  • TxtFile

When I create a new (X)Track and a (Y) file, they are independent by nature, but.. the row is a cartesian product of track & row types.

EDIT
(to be more clear): How can I have many concrete-tracks in one hand and many FileTypes in the other hand, and create well-formatted-rows (differents for every file) by tracks which have different data (columns, headers..)?
es:

  • XtrackRow(double a, double b, Calendar date) -> to -> CVSfile (tab delimited with headers)
  • XtrackRow(double a, double b, Calendar date) -> to -> TXTfile (formatted columns and title)
  • YtrackRow(double a, string, b, int c, double e) -> to CSV ..
  • YtrackRow …. -> to -> docx file (with another kind of table or tabulation)
    ..

I see two kinds of solutions:

  1. Tracks send (right)-formatted row to file: every track has to know which kind of format apply (to rows) to give them to save to any specific type of file.
  2. Tracks send raw-data to any kind of file which is responsible to format them: in this case, the file-class must know wich kind of data has to format (every track has diffrent contents, columns, headers..). Moreover, every track-class has to send different number and types of parameter..

The second solution seems to be more fitted to the Single-Responsibility principle.. but I have no Idea how to implement it.

I tried to use Bridge Pattern to solve this problem (using first solution):

abstract class Track{
  ...
  FileInterface file;

  Track(FileInterface fileType){
    this.file = fileType;
  }

  abstract String formatConcreteTrackRow();
  
  void sendRow(){
    String rowToSave = formatConcreteTrackRow();
    file.saveRow(rowToSave);
  }
}

By this way the problem is not already solved, because every concrete-track has to implement a set of methods which returs right formatted rowString: one for every file-type.
If I use a Strategy Pattern:

class SimpleTrack extends Track{
  ...
  RowFormatStrategy rowStrategy;
  
  @override
  String formatConcreteTrackRow(){
    return this.rowStrategy.getRowString("args")
}

but in this case.. every concrete-track require a different StrategyInterface, because every concrete-track has different number and types of arguments to elaborate..
If I do not use Strategy Pattern and I define a set of methods (formatCsvRow(args),formatTxtRow(args)..) I need to include a switch(fileType) loop to choose which method to use.. breaking SOLID principles.. 🙁

Moreover..
how to impose, for every new concrete-track to have right row-format methods for every existent file-template-row?
and.. How to impose, at the same time, for every new file-class to impose new templates and relative methods in every existent concrete-track?

To be honest, it’s also quite reductive impose formatConcreteTrackRow to be a String, but it’s over and over the main problem.

I’m not interested to maintain this kind of class structure, this is only the best solution I found trying to follow SOLID principles. If you can show me a better solution, my intent is to study and understand SOLID procedures to solve these kind of purposes.

(I looked around for similar questions, but I’m not even able to define the specific problem itself..)
Thank you very much.

java – Design pattern problem for class combination (cartesian product) for save format strategy

Hello to everyone and thank you for any suggestion.

I have a family of subclasses of Track (Track is abstract).
Every concrete Track has a different number and types of fields (attributes) that extend the basic abstract track-fields.

Then I have a family of file-types that implement some methods (saveRow() etc..).
Every file is presumed to have different type of row-formatting (imagine csv tabs, headers etc..)

ex:

  • SimpleTrack: double lat, double lon, Calendar dateTime.
  • DetectionTrack: (as SimpleTrack) + boolean detection.
  • ..
  • CsvFile
  • TxtFile

When I create a new (X)Track and a (Y) file, they are independent by nature, but.. the row is a cartesian product of track & row types.

I see two kinds of solutions:

  1. Tracks send (right)-formatted row to file: every track has to know which kind of format use to send formatted-row to a specific type of file.
  2. Tracks send raw-data to file which is responsible to format them: in this case, the file class must know wich kind of data has to format (headers, type of data..). Moreover, every track-class has to send different number and types of parameter..

The second solution seems to be more fitted to the Single-Responsibility principle.. but I have no Idea how to implement it.

I tried to use Bridge Pattern to solve this problem (using first solution):

abstract class Track{
  ...
  FileInterface file;

  Track(FileInterface fileType){
    this.file = fileType;
  }

  abstract String formatConcreteTrackRow();
  
  void sendRow(){
    String rowToSave = formatConcreteTrackRow();
    file.saveRow(rowToSave);
  }
}

By this way the problem is not already solved, because every concrete-track has to implement a set of methods which returs right formatted rowString: one for every file-type.
If I use a Strategy Pattern:

class SimpleTrack extends Track{
  ...
  RowFormatStrategy rowStrategy;
  
  @override
  String formatConcreteTrackRow(){
    return this.rowStrategy.getRowString("args")
}

but in this case.. every concrete-track require a different StrategyInterface, because every concrete-track has different number and types of arguments to elaborate..
If I do not use Strategy Pattern and I define a set of methods (formatCsvRow(args),formatTxtRow(args)..) I need to include a switch(fileType) loop to choose which method to use.. breaking SOLID principles.. 🙁

Moreover..
how to impose, for every new concrete-track to have right row-format methods for every existent file-template-row?
and.. How to impose, at the same time, for every new file-class to impose new templates and relative methods in every existent concrete-track?

To be honest, it’s also quite reductive impose formatConcreteTrackRow to be a String, but it’s over and over the main problem.

I’m not interested to maintain this kind of class structure, this is only the best solution I found trying to follow SOLID principles. If you can show me a better solution, my intent is to study and understand SOLID procedures to solve these kind of purposes.

(I looked around for similar questions, but I’m not even able to define the specific problem itself..)
Thank you very much.

probability – How to find an optimal strategy in an auction?

Consider an auction of sculptures by four artists: A, B, C and D. The auctioneer introduces each item to be sold in the auction room, and all bidders write their bids on a secretly sealed note that is given to the auctioneer. At the end of each round, the auctioneer announces the highest bidder as the winner, collects payment, and moves on to the next item. There are two different scenarios in this game:

  1. If there is a target set of sculptures to be purchased, the auction is won by the first bidder who purchases this set of items, and in each round, the highest bidder pays their own bid. The target set’s essential requirement is 3 sculptures by any artist, 2 by another artist, and 1 by another artist. For instance, 3 by artist B, 2 by artist A and 1 by artist D. Or another example, 4 by C, 1 by D and 3 by B (the order is irrelevant).
  2. But if there is no target set, the bidding will run until no further items are available to bid on. In this case, in each round, the highest bidder pays the second-highest bid, and the winning bidder is the one who has the largest net worth of sculptures—the sculpture of artist A is worth $3, artist B is worth $10, artist C is worth $21, and artist D is worth $50.

In both cases, the number of rounds (which is also equal to the number of sculptures) is 200, and the starting budget for every bidder is $1000. Also, at the end of each round, the previous round’s information is revealed so the bidders can use it for their benefit. The game each bot plays in each round is a one-shot game against the other bots. Each game a bot plays in each round is dependent on the games it has already played.
The information includes:

  • Details of all bidders, including their names, what they have won, and their remaining budgets.
  • A list of the full sculptures order.
  • A list of the winners’ names of each round till now.
  • List of amounts paid for items in the rounds played till now.

I should find an optimal strategy for my bot to beat other bidders (bots). My field is neither mathematics nor economics, and this problem is new to me, so I need help with the theory part based on which I will do the coding implementation. I have searched about the relevant concepts and think that the second case above must be Vickrey auction; however, I am not sure if in the Vickrey auction the remaining budget of each bidder is revealed and if the other information (above bullet points) provided for the bidders in each round, is associated with the Vickrey auction conditions. Also, in the Vickrey auction, it is always optimal for bidders to place a bid that reflects their own valuation of the good. But in this game, the bidders (bots) are provided with more information, so does the truthful bidding idea still apply? Is Nash equilibrium applicable here? Sorry to ask several questions. To encapsulate all, I would appreciate it if someone suggests the correct type of auction in each scenario explained above and helps me with some formulas/tips/references to find the optimal strategy.

Any help is much appreciated.

c# – Strategy for Chrome timing out on a Database cleanup job which takes a long time

I have a notifications app which sends users notifications. It uses a .NET core backend, an angular frontend and Cosmos DB. Within this I have an API endpoint function which is linked to a button used to clean up inactive users’ records from the DB based on a selected time period.
This database job takes a long time to complete (up to 30 minutes) and is a problem because chrome’s maximum time out limit is only 5 minutes. This prevents a “Success, users deleted” message from showing up on the client side. What strategy or workaround can I use to avoid this problem?

API Endpoint

        (HttpDelete("CleanUpInactiveUsers/{years}"))
        public async Task<IActionResult> CleanUpInactiveUsers(string years)
        {
            try
            {
                await Queries.RemoveInactiveUsers(years);
                return Ok($"Users inactive for more than {years} have been deleted");
            }
            catch (Exception ex)
            {
                return StatusCode((int)HttpStatusCode.InternalServerError);
            }
        }

Frontend call

// getUsersWithCondition(args) returns the total number of users if args="All", 
// otherwise return no. of users who haven't been active for more than args=<years>
// deleteInactiveUsers() linked with API endpoint snippet above.
.
.
.
            case this.TaskEnum.CleanInactiveUsers:{

              this._apiService.showToast(true, 'Clean Up of inactive users has begun. You will be notified once completed.');

              this._apiService.getUsersWithCondition(item.Params('time')).subscribe((response:string) =>
              {
                this.finalCount = Number(response);
              });

              this._apiService.deleteInactiveUsers(item.Params('time')).toPromise().then(res =>
                {
                    this._apiService.showToast(true, 'Deleted '+ this.finalCount + ' User(s)');
                }
              ).catch((error) => EpDialogService.error({message: 'In App Notification', description: `An error ocurred while executing the task ${ item.Task }`}));
              break;
            }

c# – How to implement a “push” strategy for a Database cleanup job?

I have a notifications app which sends users notifications. It uses a .NET core backend, an angular frontend and Cosmos DB. Within this I have an API endpoint function which is linked to a button used to clean up inactive users’ records from the DB based on a selected time period.

Right now it uses a polling strategy where once the user confirms to proceed with the cleanup, the client side polls the back end every minute and compares the no. of users before the cleanup and the number of users after the cleanup. What is the approach to take to convert this polling logic into a “pushing” logic? Refer to the code for clarity:

API endpoint (called when user confirms delete operation after specifying a time span) Controller.cs

(HttpDelete("CleanUpInactiveUsers/{years}"))
public async Task<IActionResult> CleanUpInactiveUsers(string years){
    try{
        Task.Run(async () =>
            {
                await Queries.RemoveInactiveUsers(years);     //this method deletes the records from DB
            });
        return Ok($"Users inactive for more than {years} will be deleted");
       }
    //catch block
}

component.ts

// getUsersWithCondition(args) returns the total number of users if args="All", 
// otherwise return no. of users who haven't been active for more than args=<years>

this._apiService.getUsersWithCondition("All").subscribe((response:string) =>
    {
        this.totalCount = Number(response);          //count before deleting recorded (All users).
    })
.
.
.          
// deleteInactiveUsers() linked with API endpoint snippet above.
case this.TaskEnum.CleanInactiveUsers:{
    instance.close();
    this._apiService.deleteInactiveUsers(item.Params('time')).toPromise().then(res =>
        {
            this._apiService.showToast(true, 'Clean Upbegun. You will be notified once completed.');
                  this.clicked='none';
                  //polling will take care of success message
        }
    )
    this._apiService.getUsersWithCondition(item.Params('time')).subscribe((response:string) =>
        {
            this.finalCount = Number(response);           //Number of users inactive for <time> years.
            setTimeout(() => 
                this._startTrackingUserDeletionProgress(this.totalCount,this.finalCount),5000);
        });
    break;
}

Polling every minute (component.ts)

  private _startTrackingUserDeletionProgress(totalCount: number, finalCount: number) {
    this._trackingSubscription = interval(60000)
      .pipe(
        flatMap(() => this._apiService.getUsersWithCondition('All')),
      ).subscribe((response: string) => {
        if (totalCount - finalCount != Number(response)) {        //totalCount holds value before delete
          return;                                                 //finalCount holds no. of inactive user
        }                                                         //for given time, (response) holds 
        if ((totalCount - finalCount == Number(response))) {      //latest count of all users.
          this._stopTrackingNotificationProgress(finalCount);
        }
      });
  }

  private _stopTrackingNotificationProgress(count: number) {
    this._apiService.showToast(true, 'Deleted '+ count + ' User(s)');      //Final pop-up shown to users
    this.totalCount = 0;                                                   //indicating success
    this.finalCount = 0;
    this.clicked ='auto';
    this._trackingSubscription.unsubscribe();
  }

I need to convert this polling logic into a push logic because the current set up may result in an infinite polling loop if the DB fails to delete some records. Can anyone help with the strategy conversion?

How to determine whether (and how) my design should use inheritance, composition, decoration and/or strategy

How to determine whether (and how) my design should use inheritance, composition, decoration and/or strategy – Software Engineering Stack Exchange

unity – How can I improve the current enemy spawning system for my strategy game

I’m currently making an RTS game for mobile where mini battles would take place on tiny battlegrounds.

The problem is that currently the player is spawning its units based on a currency system, whereas the enemy is spawning based on a timed system. The timed system would make sense in a tower defense game, but not here.

Here is a short clip of it in action: https://twitter.com/LewanayG/status/1378009426808950794

This is my current spawn script:

void Start()
    {
        //start spawning waves
        StartCoroutine(SpawnWaves());
    }

    IEnumerator SpawnWaves()
    {
        //before spawning the first enemies, wait a moment
        yield return new WaitForSeconds(startWait);
        while (true)
        {
            //if not all characters of this wave are spawned, spawn new enemy and that wait some time before spawning next enemy in this wave
            for (int i = 0; i < startEnemyCount; i++)
            {
                int random = Random.Range(0, enemies.Length);
                GameObject newEnemy = Instantiate(enemies(random), transform.position, transform.rotation) as GameObject;
                newEnemy.transform.parent = enemyParent.transform;
                
                yield return new WaitForSeconds(spawnWait);                         // to add some randomness between spawns
            }
            
            //make sure the next wave contains more enemies than this one
            startEnemyCount += extraEnemiesPerWave;
            //wait before starting the next wave
            yield return new WaitForSeconds(waveWait);
        }
    }

Under the current system, as long as the player has more units on the battleground than the current spawn rate of the enemy it will win, and its really difficult to balance this game based on the two spawn systems.

How do you suggest I handle this problem and the spawn system of enemy units?

DreamProxies - Cheapest USA Elite Private Proxies 100 Private Proxies 200 Private Proxies 400 Private Proxies 1000 Private Proxies 2000 Private Proxies ExtraProxies.com - Buy Cheap Private Proxies Buy 50 Private Proxies Buy 100 Private Proxies Buy 200 Private Proxies Buy 500 Private Proxies Buy 1000 Private Proxies Buy 2000 Private Proxies ProxiesLive Proxies-free.com New Proxy Lists Every Day Proxies123