PostgreSQL efficient lookup for 2 column combinations

I have a PostgreSQL (v. 12) database storing match results. I have integer columns team_home and team_away which have foreign key constraints for team_id in table teams . My question has a few scenarios:

  1. If I want to find all games involving a single team with ID 1 I could do:

SELECT * FROM matches WHERE team_home = 1 OR team_away = 1;

  1. If I wanted to find all games between 2 teams, IDs 1 and 2 I could do:

SELECT * FROM matches WHERE (team_home = 1 AND team_away = 2) OR (team_home = 2 AND team_away = 1);

  1. I want to join my matches table with a teams table:

.

SELECT m.match_id, t1.name, t2.name  FROM matches m  JOIN teams t1 ON t1.team_id = m.team_home  JOIN teams t2 ON t2.team_id = m.team_away  WHERE m.team_home = 1 OR m.team_away = 1; 

The question I have is – are these the optimal queries and can I improve efficiency?

I have read a bit about indexes and this page seems to suggest I could improve my queries across two columns like this by using a GIN index, however that is for text columns and not Foreign Key integer columns.

Alternatively should I create an ARRAY column of team ids and then use an ANY query? (The obvious downside to that is you lose the abilit

Proxy Error, DNS lookup failure for -SOS-

Hello, everyone,

please I need your professional help

I have built a new page (3 months old), and because my page speed was too low, I wanted to perform this. But I made a big mistake! I came across a platform called "Ezoic.com" and followed their instructions and changed my server entries in my DNS provider. And that was the lockdown. My website is no longer accessible, neither on the domain name nor on wp-admin. I don't know what to do anymore. I restored the settings on my server, but…

Proxy Error, DNS lookup failure for -SOS-

Constructing a data structure supporting prioritized key lookup

so this is more or less a shot in the dark as I am feeling stuck. Maybe some of you have an idea which helps.

Here is the problem description (pseudo formal):

I want to have a structure $ T = \{ \hat{x_1}, \hat{x_2}, … \}$ with $ \hat{x_i} = (p_i, k_i, v_{k_i})$ .

$ p_i \in \mathbb{N}$ can be interpreted as an associated priority. They can be considered unqiue.

$ k_i \in \mathcal{K}$ a key index, $ d := |\mathcal{K}|$ is not required to be negligibly small, though generally $ d \lt\lt |T|$

$ v_{k_i} \in \mathcal{V}^{(k_i)}$ a partial key over some universe associated with the given key index. As a little pace killer, this key may not be hashable. The only requirement is totally ordered.

Considering an array $ y = [v_i], i \in \mathcal{K}$ , the structure should be capable of supporting the following operations efficiently:

$ lookup(T, y) \rightarrow \underset{x_i \in T : y[k_i] = v_{k_i}}{arg max}( p_i )$ , i.e. the node $ \hat{x_j}$ with a partial key matching and the highest associated priority.

$ succ(T, y) \rightarrow lookup(T \backslash \{\hat{x_j}\}, y)$ , i.e. the successor (in terms of priority) of a node $ \hat{x_j}$ matching $ y$

Ultimately I would also like efficient insertion and deletion (by priority).

(For insertion: The selection of $ k_i$ for each node is another point of research and can by chosen at the time of insertion. It is essentially possible that this key index is subject to change if it helps the overall structure. But it is also not required that all key indices in $ \mathcal{K}$ are supported for each node, i.e. one might need to insert a node which is only queryable by a single $ k_i$ .)

The key indices are somewhat causing a headache for me. My research so far includes standard priority search trees and dynamic variations thereof. I also found this very interesting, though very theoretical, paper which takes care of the increased dimensionality (kind of) caused by the d-dimensional keys.

I know that I can construct d-dimensional binary search trees with a query complexity of $ O(d + n log(n))$ . These should definitely yield a gain though I am not capable of mapping the priority problem to it (Ref).

But I guess the complexity can be reduced even further if we consider that fact, that each node is only storing partial keys anyway.

My approach so far is somewhat naive as it simply creates a hash map for each key index in $ \mathcal{K}$ and queries each hash map upon lookup. It then aggregates all the results and sorts them by priority. This works fine for hashable keys but a fallback structure has to be used whenever they are not (I am using binary search trees).

How does a TLB lookup compare all keys simultaneously?

I am reading OS Concepts dinosaur book which says, “Each entry in the TLB consists of two parts: a key (or tag) and a value. When the associative memory is presented with an item, the item is compared with all keys simultaneously.”

I checked How does a TLB and data cache work? but it does not say anything about the implementation that allows this parallel checking of the keys.

I read something about a parallel hash table here: http://www.cs.cmu.edu/afs/cs/academic/class/15210-s12/www/lectures/lecture27.pdf

Is this the basic idea? The insertion of a key outputs a frame number and this could either be a hit or miss?

Efficiently populate a look-up table for a function over a range of arguments in Python

I am minimizing a scalar function which takes a n-dimensional vector input and outputs the scalar value, n-dim vector (Jacobian), and an nxn matrix (Hessian). Given a range of the elements in my input vector I’m looking for a efficient way to precalculate the outputs in an efficient to access format.

I’ve been thinking of a scheme based on numpy.interpn with a regularly spaced grid of inputs, but this only allow for linear interpolation of intermediate values, and requires regular sampling.

I’m hoping there is a tool available that does this with a more intelligent method? Perhaps with automatic refinement of inputs sampled or a more sophisticated interpolation scheme?

Technically, the scalar output contains the Jacobian and Hessian but I need those with decent fidelity, so I would either need a higher order representation of the scalar function (with added sampling frequency) or I ccan interpolate on the Jacobian and Hessian directly (as they are outputted by the minimizer anyway)

Thanks

RID Lookup on table with one column?

This is a simplification of a real cursor query:

DECLARE @T table (a int NOT NULL INDEX ia UNIQUE);  INSERT @T (a) VALUES (1), (5), (25), (75);  DECLARE c CURSOR LOCAL      SCROLL DYNAMIC OPTIMISTIC TYPE_WARNING FOR SELECT T.a FROM @T AS T ORDER BY T.a ASC FOR UPDATE OF a;  OPEN c;  FETCH RELATIVE 2 FROM c;  CLOSE c;  DEALLOCATE c; 

The execution plan for the FETCH shows an RID Lookup:

enter image description here

I am having trouble understanding how this plan produces the result specified by the statement:

FETCH RELATIVE 2 FROM c; 

I am most puzzled by the RID Lookup because the table has only one column, and that is returned by the Index Scan.

A detailed explanation of how the plan works would be most appreciated.

Lookup Column in SharePoint Designer 2013 Workflow then send an email to the users for infos

Dears, I am a new user to the SharePoint designer and I need to do workflow to my Lookup list (it has Multiple values to chose ) ; then send email to different users when they use the Lookup list .. I am getting the email like this (5 and {“results”:[{“Id”:5,”Value”:”Narrow band IoT “}]})

any hep 🙂

Look-up Columns redirecting to folders changed their behaviour

I am using SharePoint Online site.

Some months ago I created a lookup column in document library [A] that referred to another document library [B]. This was the best option I found to be able to refer attachments to each documents in [A], using in [B] a field that is filled only for folders and not for documents.

It was working fine, when clicking on the look-up field, it used to give the possibility of checking the documents directly in a pop-up window or to open the folder in another view.

Since a couple of months this doesn’t work anymore, now when clicking on the look-up field, it opens the “Properties of the folder” (that means just the name of it) and doesn’t show the content of the folder itself.

What’s changed? How can I solve this?

Too many lookup columns error – stable list with no new lookups being created or used

Users & ‘Full Control’ site collection admin are unable to EDIT items.

Error message reads:

The query cannot be completed because the number of lookup columns it contains exceeds the lookup column threshold enforced by the administrator

I understand how this issue is normally triggered – if more than 8 lookup columns (I’ve also read of a max of 12) are used, it creates an error. I’m using SP2013.

However, in my scenario, the list in question is long-established and is stable (in terms of development – no new lookup columns are in use). Hence, I’m somewhat puzzled as to what has triggered this error.

I’m using content types, one of these only has 6 lookups in it, I tried to edit one of these but I get the same error.

I did have some Windows Updates install yesterday, I’ve no idea if that would be the cause. Windows Updates are regular, so normally I’d exclude it as being the cause of anything, but given the otherwise stable nature of the list, I’ve been trying to think up other possible causes.

Product content type pic:

8 lookups/’person or group’ columns

enter image description here

Other EXP content type pic:

Only 6 lookups/’person or group’ columns

enter image description here

23/09/2019 – additional info

  • a different farm administrator to me could still edit items, while I could not (despite us having identical permissions). Suggesting he does not have the latest Windows Updates installed yet(?)

  • Following Peter’s recent comment regarding workarounds. In my ‘OTHER EXP’ content type, which has 6 ‘Lookup’ or ‘Person or Group’ fields, I went in CT settings and hid a lookup field, so only 5 of these field types remain in the edit form; the edit form worked correctly. However, for the 8 field Product content type, this workaround did not help

  • On the server, in Sharepoint Central Administration, I observed that the default number of lookup fields is set to 8. Therefore this setting is somehow being overriden, since the List will not display the edit item form.

  • Not all lists are affected; in a different site collection a list with 7 lookups is still functioning normally.

24/09/2019 – in response to Slaven’s comment about database version:

Database version is 15.0.5023.1000:

Sharepoint Farm - 2013, database version 15.0.5023.1000

Further testing

Saved the list as a template with contents, with the intention of trying to recreate it. When I tried to create a new list (including contents) based on this template, I got this (Windows?) error.

Error message - Windows

This website lists lots of error codes for Microsoft tech, wow what a find, specifically it included my error message too. I’m using Windows 7, my colleagues are using newer versions of Windows.

Newer version of Windows required?