![]() Why would the PostgreSQL Global Development Group (PGDG) bother to maintain "bad" options?Ī better question would be, "Which is best for this case?". We have often been asked which method is "best." If there were a clear answer to that question, the "worst" ones would be removed from the list of options. The query parser is largely a strategy-picking mechanism that maps your query to the functions in this table.Įach of these computational strategies for joining tables has merits and drawbacks. This is the system table where the implementations of all of the functions of PostgreSQL reside. These functions are listed in pg_catalog.pg_proc. That is, four basic algorithms to match data from one set to another. There are four basic join types in PostgreSQL. ![]() In further articles, we'll tackle some of the more complex types of joins. The data may fall within a range, a list, a distribution, a non-equality, or just let everything match everything else ( Cartesian).įor the sake of simplicity and brevity, we will concern ourselves here with joins based on equality. That is the most common way data is related in a basic relational model-but it is by far not the only way to structure a relationship. Generally, we think of these criteria as being equality joins. It does this by picking a join type, an algorithm written in C that performs the checking required to see if the data is indeed related in the way the user requested. This is where the parser has to decide on the most efficient way to relate the data together based on the qualifications. More ways being thought up by developers all the time.Īnd now, we get to the magic related to our article title. Build a hash of the criteria, then scan the data and apply the hash in memory.Ĩ. Index-only scan (requires the selection list to exist in the index).Ħ. Scan the table while using the index to eliminate rows.ĥ. Use an index to retrieve only the requested data.Ĥ. Scan the tables while applying the criteria directly to each row.ģ. Retrieve all of the data and apply the criteria in memory.Ģ. The qualifications list narrows the search to specific data the caller is interested in.Įach entity in the "scan list" is called a "scan node." When we look at this from an execution point of view, there are several ways in which the parser can retrieve the data the user requested (the selection list) from the scan node(s):ġ. Then we have the "qualifications list," which is: ![]() This tells the parser which storage the selection list comes from. More importantly to our join types discussion, we have the "scan list": This is the list of items to retrieve from the physical data storage. In this statement, we have a section that is called the "selection list": id ) WHERE criteria = 1 and more_criteria = 4 Copy SELECT fields, cols, tuples, list, item Let's take a look at a fairly simple statement that gives us most of the elements that the parser uses: The objective is to take apart an SQL statement and turn it into a list of functions that will produce the requested result. The basic working of a database planner is not as complex as you might think. This article will explain in a bit more practical detail how the query planner deals with the complexity of SQL join theory. We won't bother to reproduce work that has been well done in the past. If you don't know what set theory is or how basic joins map to set theory, there are some really good articles already out there. You can look forward to some subsequent articles that explain in more detail where and when different types of joins are helpful and provide the most efficiency. This article will go over the high-level strategy of the PostgreSQL parser for choosing join types. That explanation is still relevant today to understand how the PostgreSQL parser picks a join method or join types. There is a lot to explain about how that ended up working. In a previous article, we mentioned that PostgreSQL came about because some mathematicians wanted to map set theory to a file system. How does the PostgreSQL parser chooses which JOIN types to use? Join us for a session on JOIN theory.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |