problem and problem solving pdf

Problem Solving and Decision Making

Cite this chapter.

• Linda Drake Gobbo 3  

Part of the book series: Advances in Group Decision and Negotiation ((AGDN,volume 3))

7982 Accesses

Problem solving and decision making in multicultural work teams are the last of the skill areas to be covered in this book. This topic will be discussed from the cultural, individual, and organizational levels of multicultural team development, building on the frameworks that have been presented in previous chapters. Many theorists consider problem solving and decision making as synonymous-all decisions are made in response to a problem or opportunity. Simply stated, if problem solving is the process used to find a solution to the problem, challenge, or opportunity. However, how one solves problems can be quite varied. An individual can use analytical tools based on logic, deduction, or induction, or intuition based on an understanding of principles, or creative thinking. Problem-solving abilities and approaches may vary considerably, actually using different paradigms or frameworks. In this chapter one approach, with the steps and methods to do problem solving in work teams, will be presented.

There are six steps to the problem-solving model described and demonstrated in this chapter. Several of those steps within the model are used for decisionmaking, and are covered as well. How a team makes the decision, and who on the team makes it are important elements and will also be discussed. As prior chapters have noted, membership of multicultural teams varies greatly. The procedures each member follows, the different value orientations guiding their behavior (Smith et al. 2002), the nature of the tasks they must complete, and the communication tools they employ (face-to-face and/or technology-based) all impact how they approach problem solving and decision making. When done effectively, problem solving, which includes decision making, moves through all the steps described here equally, engaging the knowledge and skills of all team members.

This chapter will first present theoretical frameworks for problem solving, then define the steps that comprise problem solving and decision making within them. This will be followed by a discussion of the cultural variations, and impact of individual styles and societal assumptions on decision-making. Shared mental models and consensus are offered as methods to equalize participation in team decision making, and an overview of other methods provided. The last section will look at ways to coordinate the stages of team development with the variety of problemsolving and decision-making techniques in order to maximize a team’s effectiveness.

This is a preview of subscription content, log in via an institution to check access.

Access this chapter

Subscribe and save.

• Get 10 units per month
• Download Article/Chapter or eBook
• 1 Unit = 1 Article or 1 Chapter
• Cancel anytime
• Available as PDF
• Read on any device
• Instant download
• Own it forever
• Compact, lightweight edition
• Dispatched in 3 to 5 business days
• Free shipping worldwide - see info
• Durable hardcover edition

Tax calculation will be finalised at checkout

Purchases are for personal use only

Institutional subscriptions

Unable to display preview.  Download preview PDF.

Similar content being viewed by others

Leading Global Teams Means Dealing with Different

The anatomy of an award-winning meta-analysis: Recommendations for authors, reviewers, and readers of meta-analytic reviews

Unraveling the effects of cultural diversity in teams: A retrospective of research on multicultural work groups and an agenda for future research

Adler, N.J. (2002). International Dimensions of Organizational Behavior (4th ed.). Cincinnati, OH: South-Western.

Google Scholar  

Cooperrider, D.L., Whitney, D. and Stavros, J.M. (2005). Appreciative Inquiry: The First in a Series of AI Workbooks for Leaders of Change. Brunswick, OH: Crown Custom Publishing.

Enayati, J. (2001). The research: Effective communication and decision-making in diverse groups. In Hemmati, M. (Ed.), Multi-Stakeholder Processes for Governance and Sustainability-Beyond Deadlock and Conflict. London, England: Earthscan.

Gardenswartz, L. and Rowe, A. (2003). In L. Gardenswartz and A. Rowe (Eds.), Diverse Teams at Work: Capitalizing on the Power of Diversity (1st ed.). Alexandria, VA: Society for Human Resource Management.

Halverson, C.B. (2004). Effective Multicultural Teams (5th ed.). Brattleboro, VT: School for International Training.

Harrington-Macklin, D. (1994). The Team Building Tool Kit: Tips, Tactics, and Rules for Effective Workplace Teams. New York: American Management Association.

Hofstede, G. (1980). Culture’s Consequences: International Differences in Work-related Values. Thousand Oaks, CA: Sage.

Janis, I. (1982). Groupthink: Psychological Studies of Policy Decisions and Fiascos (2nd ed.). Boston, MA: Houghton Mifflin.

Jeffery, A.B., Maes, J.D. and Bratton-Jeffery, M.F. (2005). Improving team decision-making performance with collaborative modeling. [Electronic version]. Team Performance Management, 11 (1/2), 40–50. Retrieved December 20, 2005, from the Emerald In sight database.

Article   Google Scholar  

Kayser, T.A. (1994). Building Team Power: How to Unleash the Collaborative Genius of Work Teams. New York: Irwin.

Kelly, K.P. (1994). Team Decision Making Techniques. Irvine, CA: Richard Chang Associates.

Kline, T. (1999). In M. Holt, D. Ullius and P. Berkman (Eds.), Remaking Teams: The Revolutionary Research-based Guide That Puts Theory into Practice. San Francisco, CA: Jossey-Bass/Pfeiffer.

Magruder Watkins, J. and Mohr, B.J. (2001). Appreciative Inquiry, Change at the Speed of Imagination. San Francisco, CA: Jossey-Bass/Pfieffer.

Mathieu, J., Heffner, T., Goodwin, G., Cannon-Bowers, J. and Salas, E. (2005). Scaling the quality of teammates’ mental models: equifinality and normative comparisons. Journal of Organizational Behavior, 26, 37–56.

McFadzean, E. (2002). Developing and supporting creative problem-solving teams: Part 1—a conceptual model. [Electronic version]. Management Decision, 40(5), 463–475. Retrieved December 20, 2005, from the Emerald Insight database.

McFadzean, E. (2002). Developing and supporting creative problem solving teams: Part 2-facili-tator competencies. [Electronic version]. Management Decision, 40(6), 537–551. Retrieved December 20, 2005, from the Emerald Insight database.

McKenna, R.J. and Martin-Smith, B. (2005). Decision making as a simplification process: New conceptual perspectives.[Electronic version]. Management Decision, 43(6), 821–836. Retrieved December 20, 2005, from the Emerald Insight database.

Sagie, A. and Akcan, Z. (2003). A cross-cultural analysis of participative decision-making in organizations. Human Relations, 56(4), 453–473.

Selart, M. (2005). Understanding the role of locus of control in consultative decision-making: A case study. [Electronic version]. Management Decision, 43(3), 397–412. Retrieved December 20, 2005, from the Emerald Insight database.

Simon, T., Pelled, L.H. and Smith, K.A. (1999). Making use of difference: diversity, debate, and decision comprehensiveness in top management teams. Academy of Management Journal, 42 (6), 662–673.

Smith, P.B., Peterson, M.F. and Schwartz, S.H. (2002). Cultural values, sources of guidance, and their relevance to managerial behavior. [Electronic version]. Journal of Cross-Cultural Psychology, 33(2), 188–208.

Tomlinson, S. (1999). Comparison of consensus Japanese style and Quaker style. [Electronic version]. Retrieved July 1, 2006, from http://www.earlham.edu/~consense/scott2.shtml

Watkins, J.M. and Mohr, B.J. (2001). Appreciative Inquiry: Change at the Speed of Imagination. San Francisco, CA: Jossey-Bass/Pfeiffer.

Whitney, D. and Trosten-Bloom, A. (2003). The Power of Appreciative Inquiry: A Practical Guide to Positive Change. San Francisco, CA: Berrett-Koehler.

Download references

Author information

Authors and affiliations.

School for International Training, Brattleboro, VT, USA

Linda Drake Gobbo

You can also search for this author in PubMed   Google Scholar

Editor information

Editors and affiliations.

Claire B. Halverson  & S. Aqeel Tirmizi  & 

Rights and permissions

Reprints and permissions

Copyright information

© 2008 Springer Science + Business Media B.V

About this chapter

Gobbo, L.D. (2008). Problem Solving and Decision Making. In: Halverson, C.B., Tirmizi, S.A. (eds) Effective Multicultural Teams: Theory and Practice. Advances in Group Decision and Negotiation, vol 3. Springer, Dordrecht. https://doi.org/10.1007/978-1-4020-6957-4_9

Download citation

DOI : https://doi.org/10.1007/978-1-4020-6957-4_9

Publisher Name : Springer, Dordrecht

Print ISBN : 978-1-4020-6956-7

Online ISBN : 978-1-4020-6957-4

eBook Packages : Business and Economics Business and Management (R0)

Share this chapter

Anyone you share the following link with will be able to read this content:

Sorry, a shareable link is not currently available for this article.

Provided by the Springer Nature SharedIt content-sharing initiative

• Publish with us

Policies and ethics

• Find a journal
• Track your research

Loading to The Art Of Problem Solving Volume 1 Sandor Lehoczky....

Academia.edu no longer supports Internet Explorer.

To browse Academia.edu and the wider internet faster and more securely, please take a few seconds to  upgrade your browser .

Enter the email address you signed up with and we'll email you a reset link.

• We're Hiring!
• Help Center

Handbook on Problem Solving Skills

Related Papers

Eglantina Zyka

In this article will be treated different skills for public administrators, during the process of decision making, which affect the effectiveness of their decisions, and thus will also affect the fulfillment of the objectives of the organizations they lead. The main purpose of the article is to identify the skills that demonstrate public administrators during decision making in order to define how good are their decison making process. On the basis of secondary and primary research, the authors would discuss the findings in order to identify the problems faced during these processes of decision-making public administrators

Sharon Gilad

Qualitative methods are relatively scarce in public administration research. This imbalance between qualitative and quantitative methods poses three significant concerns. First, there is a risk that measurement hurdles, coupled with the distance that quantitative methodology fosters between academics and administrative practice and practitioners, focuses researchers' gaze on inconsequential policy problems. Second, and related, the causality underlying the real problems that policymakers and public organizations face are often much too complex to be captured by one type of methodology, and quantitative methods, which are by design insensitive to context, are particularly ill adapt to such task. Third, quantitative methodology is most conducive to testing already available theories and hypotheses, as opposed to theory building. We propose that the answer to these concerns lays in denouncing commitments to abstract ontological and epistemological philosophies and advancing collaboration between qualitative and quantitative researchers and version of mixed methods that transcend mere triangulation. Still, doing so would tell us little as to what problems are worth studying, and how to balance the values of theoretical generalizability and policy significance.-1

Tom van Engers

ETS Research Report Series

Leonard Baird

European Journal of Human Resource Management Studies

Clive Hunter

Alberto Maydeu-Olivares

In this paper we discuss how the interests and field theory promoted by public administration as a stakeholder in policy argumentation, directly arise from its problem solving activities, using the framework for public administration problem solving we proposed in [1,2]. We propose that calls for change of policy in public administration mainly arise from model-based diagnosis problem solving.

Mathematics Education Research Journal

Margaret Taplin

Bilal AYGUN

The rapid change in cultural and social spheres, rapid urbanisation, industrialization, the unattainable speed of technology, general educational shortcomings, increasing family divisions and divorces, the growing ideological, religious, and ethnic conflicts within the country, corruption and corruption, the efforts to preserve the traditional value system and way of life, as well as adapting to the modernization process, and for many more reasons, today's people face constantly changing problems.

Edgardo García

Loading Preview

Sorry, preview is currently unavailable. You can download the paper by clicking the button above.

Press Herald

Account Subscription: ACTIVE

Questions about your account? Our customer service team can be reached at [email protected] during business hours at (207) 791-6000 .

Opinion: Competent caring and problem solving go hand in hand

The world's leaders could learn some important lessons from the dedicated staff at Miles Hospital in Damariscotta.

You are able to gift 5 more articles this month.

Anyone can access the link you share with no account required. Learn more .

With a Press Herald subscription, you can gift 5 articles each month.

It looks like you do not have any active subscriptions. To get one, go to the subscriptions page .

Loading....

Caring is the key to happiness, success and respect alike. The capacity to care is a universal human potential that must be developed and nurtured as part of our spiritual, intellectual, ethical substance. Failing that, we remain – in my father’s words to me, back under Hitler’s rule – “lightweight for a grownup size.” Indeed, just like flotsam rises after a ship has sunk, lightweights around me have kept rising to the top as institutions and societies compromised their values and started sinking. Naïve believers often mistake such rises for “success,” even inspiration.

As the home stretch of this year’s elections unfolds, we hear calls for saving democracy and honest politics, even as we sometimes wonder whether a world order based on such honesty is even possible. Actually, there are working models of honest, caring, democratic collaborations, ready to be adopted and upscaled.  My family and I found such a model in Damariscotta’s Miles Hospital when my wife, in pain, checked in at their emergency room in the middle of the night. Her diagnosis: appendicitis. During surgery the appendix was found to have burst, prolonging the post-operation recovery.

ABOUT THE AUTHOR

Paul F. Kando is a resident of Damariscotta.

Post-operative care was an exemplary combination of science, skill and care, delivered by a multiracial, multiethnic nursing staff – from north, south, Canada, Haiti, Colombia, the Philippines and more. They kept track of vital signs and healing progress, keeping the doctors informed. And they cheerfully kept their patient as comfortable as possible. Beth was released from the hospital eight days after her surgery.

What we experienced is also a great model of successful problem solving, one that I, as an engineer, can sum up as the keys to creative competency. They are:

(1) Conduct research. Peruse prior medical-scientific findings, diagnostic tests, monitoring patient progress. Research is the only reliable way to discover reality – e.g., the appendicitis diagnosis and the results of patient monitoring – the only things that matter. From private lives to unfolding world history, everything is a series of real occurrences.

(2) Truth – e.g., the information shared by members of the medical team – is simply information that describes reality. Misinformation, propaganda, beliefs, denials, lies, and so on, are but damaging distractions from truth and reality, with potentially dire consequences. Advertisement

(3) Questioning and critical evaluation – how members of the medical team decide the appropriate treatment and medications, for example – are the only way to ascertain the truth, and the validity of information from any source.

(4) Respect must be earned by individuals, officials and institutions alike. They earn it by being truthful and caring about the needs of others. Mutual respect is what makes professional collaborations, like those at Miles, possible. Liars, narcissists, hiders behind assorted uniforms may demand, but cannot earn respect.

(5) Equality and mutual respect are the only basis on which diverse people can peacefully and productively work together. We humans are, without exception, equals as members of small minorities within Earth’s great population – of one diaspora among many that may or may not earn the acceptance and respect of the others as citizens of this diverse, multicultural world. This diverse equality is both a comforting fact and a challenge.

I have found the above five key principles applicable to all problems I have been challenged to address. The same simple principles are key to addressing climate change, the Gaza fiasco and this 2024 election season. They define and demonstrate what’s possible. So, the Miles Hospital medical team has much to teach leaders, public servants and citizens alike on both the national and international levels – to act with care, and only on the basis of reality and truth; and to avoid falsehoods, power games and demagoguery. Because only real things matter, i.e., only the facts, uncontaminated by falsehoods, power games and manipulated beliefs.

Sick climate? Ailing country? Warmongering? Demagoguery? Endangered democracy?

What and who could stop us from being diverse, committed caregivers for this country working together?

We invite you to add your comments, and we encourage a thoughtful, open and lively exchange of ideas and information on this website. By joining the conversation, you are agreeing to our commenting policy and terms of use . You can also read our FAQs . You can modify your screen name here .

Readers may now see a Top Comments tab, which is an experimental software feature to detect and highlight comments that demonstrate compassion, reasoning, personal stories and curiosity, and encourage and promote civil discourse.

Join the Conversation

Please sign into your Press Herald account to participate in conversations below. If you do not have an account, you can register or subscribe . Questions? Please see our FAQs .

Your commenting screen name has been updated.

Send questions/comments to the editors.

« Previous

Opinion: Why Catholics should vote Democratic in 2024

Member Log In

Please enter your username and password below. Already a subscriber but don't have one? Click here .

Not a subscriber? Click here to see your options

More From Forbes

Nice’s 1cx could be a game-changer for solving knotty customer problems.

• Share to Facebook
• Share to Twitter
• Share to Linkedin

Imagine that one of your customers is experiencing a problem with a product or service that they have purchased from your company. Wanting to find a fast and easy solution to their problem, they land on your website, but after searching your FAQs and interacting with your chatbot, they realise they won’t be able to find the answer to their problem. It’s a little too complex.

They then decide to call your contact center to seek help. Once they get through to an agent and explain their issue, the agent quickly realises that the customer’s problem is quite complex and not something that they themselves will be able to help with. It will require the assistance of one of their colleagues in the back office. However, rather than asking them to call another team or placing them in another queue, they remember that they helped a customer with a similar issue last week and, in particular, how one of their colleagues in the back office team had been a great help.

Asking the customer to bear with them for a moment, the agent checks their system to see if that colleague is available and quickly messages them to see if they can help. Their colleague quickly responds saying that they can help, and the agent proceeds to loop their colleague into the conversation. Introducing their colleague to the customer and explaining the situation, the agent then takes a back seat in the conversation whilst their colleague helps the customer. This takes a few minutes, so while they wait for the issue to be resolved, the agent catches up with some administrative tasks and answers a few emails from other customers.

Once the customer’s issue is resolved, the agent jumps back into the conversation, thanks their colleague for being able to help and rounds off the interaction by asking the customer if there is anything else that they can help them with today. The customer answers that there is nothing else, and thanks the agent and their colleague for being so helpful. They both bid each other a good day. The call ends.

Customer support agent

I recently played this scenario to Barak Eilam, CEO of NICE , and he explained that the type of connectivity and collaboration described in the scenario is exactly what 1CX , their new product line, aims to facilitate and is something that many of their customers have been asking for for some time now.

Best High-Yield Savings Accounts Of 2024

Best 5% interest savings accounts of 2024.

1CX is ostensibly a Unified Communications as a Service (UCaaS) product addition to their existing platform, but its launch recognizes a couple of trends that are emerging in the customer service and experience space, namely:

• Digital, automation and self-service technology will mature and improve in scope and effectiveness. This will remove much of the need for agent involvement in the majority of simple and straightforward inquiries from customers.
• As this happens, the nature of the queries that customer service agents will be asked to handle will increase in complexity. Some of these more complex queries will require access to knowledge, systems, and expertise that is often not accessible on the front line but, more often than not, sits in the middle and back offices of large organizations.

Over and above the capability of their new product and what it will allow contact centers to do, particularly when faced with complex customer problems, one of the most interesting things about 1CX is how they are pricing it. Rather than pricing it like a separate UCaaS-lite offering and charging an additional $25 per seat, as is normal with some other systems, existing CXOne clients will only be charged an additional $5 per user per month for this set of enhanced collaboration and communications capabilities.

This is a smart move, particularly given the budget and investment constraints that many contact centers have to operate under, and is likely to prove popular.

• Editorial Standards
• Reprints & Permissions

Join The Conversation

One Community. Many Voices. Create a free account to share your thoughts. 

Forbes Community Guidelines

Our community is about connecting people through open and thoughtful conversations. We want our readers to share their views and exchange ideas and facts in a safe space.

In order to do so, please follow the posting rules in our site's  Terms of Service.   We've summarized some of those key rules below. Simply put, keep it civil.

Your post will be rejected if we notice that it seems to contain:

• False or intentionally out-of-context or misleading information
• Insults, profanity, incoherent, obscene or inflammatory language or threats of any kind
• Attacks on the identity of other commenters or the article's author
• Content that otherwise violates our site's  terms.

User accounts will be blocked if we notice or believe that users are engaged in:

• Continuous attempts to re-post comments that have been previously moderated/rejected
• Racist, sexist, homophobic or other discriminatory comments
• Attempts or tactics that put the site security at risk
• Actions that otherwise violate our site's  terms.

So, how can you be a power user?

• Stay on topic and share your insights
• Feel free to be clear and thoughtful to get your point across
• ‘Like’ or ‘Dislike’ to show your point of view.
• Protect your community.
• Use the report tool to alert us when someone breaks the rules.

Thanks for reading our community guidelines. Please read the full list of posting rules found in our site's  Terms of Service.

Register for Simple Talk

Get the latest Opinion, Podcasts, Webinars, and Events, delivered straight to your inbox.

Solving the Overlap Query Problem in PostgreSQL

Share to social media

Range queries are a very common task in SQL: selecting dates, numbers, or even text values that fall within some specified range. For instance: all loan applications for the month of March, or all sales transactions between $50 and $500. SQL developers are generally adept at writing such queries and indexing them to ensure they perform well.

But when the requirements are changed slightly — to compare two ranges of values to find where they overlap — it’s suddenly a whole new ballgame. These so-called “overlap queries” are trickier than they appear.

Overlap Queries

The most common overlap queries involve temporal (date and time) ranges, known as intervals . For example, imagine a query for patients at a hospital. If you wish to find all patients initially admitted during the month of August, that’s a simple range query. But if you wish to find all patients whose duration of stay — another date interval — lies at least partially in the interval of August, that’s an overlap query. While writing such queries isn’t hard, writing them to perform well can be difficult. Let’s see why.

A Use Case Scenario

Let’s examine an actual problem involving an overlap query. The database for a large museum tracks the time all visiting patrons enter and leave the building. It also maintains the times of all outages in its video security system. After a rash of minor thefts, the head of security has asked for a list of all patrons who were present during one or more security outages. Clearly, we have one datetime range, the length of a patron’s stay, which must be compared with another, the interval denoting a security outage. The goal is to find all overlapping instances.

The Database.

A table of patrons contains an initial and final timestamp indicating the duration of the visit and an id indicating the associated patron. And an outages table similarly defines the start and end timestamps of each outage. For simplicity, we include no other columns.

To test performance on a large dataset, we’ll populate both tables with 20 years of random data. (The script to fill the tables is in the Appendix section that follows the Conclusion section at the end of the article.)

In SQL, the canonical way to compare intervals is with the OVERLAPS() operator. To find all cases of overlap, simply form the dates from each table into intervals, then compare with this operator:

This query works — it retrieves the appropriate rows, at least — but it’s extremely slow. (On my test system (Windows 11 with PostgreSQL 16, test machine is an AMD 5700x CPU, 2GB SSD, Windows 11), it takes 3+ minutes). A first attempt to optimize the query might lead you to create a composite index on outage start and end time, and a similar one on patrons:

No help: the query is just as slow A little research finds that the OVERLAPS operator doesn’t support indexes. So instead, we specify the comparisons explicitly.

The first time you attempt a query like this, you’ll likely draw lines on a sheet of paper and see there are four different ways two intervals can overlap (see graphic.) This makes a rather complex query, but we can simplify it with a little trick. There are only two ways that intervals don’t overlap. If we write the non-overlapping query, then invert with a NOT, we get the desired result.

For example, a WHERE clause to find all non-overlapping cases for interval1 and interval2 looks like this:

If we invert the entire query with NOT , we find all cases of overlap

This simplifies (using Boolean logic rules) to:

(Note: The query is written to conform to the SQL standard on temporal intervals, which states that overlap occurs if one interval starts exactly when the other ends. This means that values that overlap by exactly 0.00 seconds are included. If you wish to require a minimum amount of overlap, adjust your queries accordingly)

So, what did we accomplish? Well, the explicit form is somewhat faster (26.1 secs), but an actual production table can be orders of magnitude larger than these samples.. We’d expect a query like this to be lightning-quick. Examining the access plan, we see PostgreSQL combining a table scan with an index scan — no indexed lookups are being used.

In a final bout of desperation, you might break the composite indexes each into two separate indexes: one for each timestamp. This not only doesn’t help, but (if you removed the original index) it will be just as slow as the original OVERLAPS() form. What’s wrong?

The Trouble With B-Trees

Like the name suggests, a B-Tree index contains a branching structure of values on the indexed column; traversing the tree allows one to find a specific value or any contiguous range of values. For composite (multi-column) indexes, the second column affects ordering only where the first column has multiple equal values: the second column acts as a “tie breaker”, as it were. Similarly, the third column comes into play only for ties on the second, and so on. This is easier to understand visually. See the following image:

Notice that our compound index (start date + end date) has little effect on row ordering compared to the simple single-column index. And neither one groups rows in a manner that allows us to quickly find all overlapping values. Rows which overlap our query range (shown in green ) aren’t in order, and so our index doesn’t help. The entire table must be scanned.

That’s bad enough when that interval is a fixed value, but when the interval comes from another table (or a self-join on the original table), it means every row in Table A must be matched against every row in Table B. In technical terms, this is quadratic performance — doubling the number of rows means the query must do four times the work. On very large tables, a query like this will hamstring even the most powerful servers.

A Cheap Trick

Before we look at a more sophisticated approach, there’s often an easy way to “hack” overlap queries. The idea is to give the QO (query optimizer) information that we have, but it doesn’t. In our example, the tables span years of timestamp values, but any single event is much shorter.

If we can identify a maximum duration for either type of event and pass that information onto the QO, it can scan a much smaller portion of the index. For example, in our data, no patron visit is longer than 4 hours. We can add this information to our query in the following manner:

The new clause (suffixed with –<<) means there is now both a minimum and a maximum value on p.enter . This converts the statement into an (index-serviceable) range query. Assuming you still have an index on this column, the effects on query time is dramatic: it shrinks all the way from 26 seconds down to 0.1 second. Wow! Suddenly we have a practical query.

Instead of setting a maximum range on patrons, we could have done the same for outages. Or better, both tables. The query optimizer won’t use both at once, but it will use the one it believes to be most efficient.

The great advantage of this technique is that it allows you to use the indexes you likely already have. However, be careful! If any data ever exceeds this maximum duration, it will be incorrectly filtered from the results. For instance, a patron row with a duration of 6 hours will be invisible to this query, no matter how many outages it overlaps. If you use this method, ensure to base your durations on business rules unlikely to change, rather than simply assuming future events will be similar to those in the past.

GiST: The Way Forward

PostgreSQL offers an alternative to BTree indexes, called GiST. You may have encountered GiST indexes in connection with querying two-dimensional data like geometric objects or geographic regions. However, these indexes are equally adept at indexing one-dimensional date, time, and numeric ranges. GiST, which stands for Generalized Search Tree , is exactly what we need for cases like this.

To create the necessary index for our example, we form the two timestamps into a range type with the tsrange() function:

GiST indexes also uses a special syntax to compare ranges for overlapping values: the ‘ && ‘ operator. Using this operator, our query now looks like this:

That’s all there is to it. This query now runs in an execution time of 0.56 secs; nearly as fast as our “hack” from above. And unlike that method, this works correctly in all cases, even if some events have very long durations. There’s no need to worry about the occasional longer-duration event being incorrectly filtered out.

The GiST index converts our original quadratic-performance query to linear performance. We can see that by increasing our sample data row counts by a factor of 10. With one million patrons and 250,000 outages, the query time increases by the same factor, to 5 seconds, whereas the OVERLAP() query form would have taken several hours. But for extremely large tables, linear performance may still be too slow.

In Part II of this article, we’ll examine the possibility of further improvements.

Conclusion.

A seemingly simple operation in SQL like finding overlapping values has some surprising pitfalls. Understanding how to form and optimize queries like this is something all SQL developers should know. Using the techniques above, you can achieve large performance gains, and satisfy performance-hungry users.

Appendix: Script to create Initial DB and populate with random data

Load comments

Recommended for you

PostgreSQL Partitioning: The Most Useful Feature You May Never Have Used

Partitioning in PostgreSQL was for many years a feature almost entirely ignored, and even today is often thought of only...

MySQL vs PostgreSQL: Which Open-Source Database is right for you?

When I joined a growing startup company as a backend developer, we were at crossroads choosing between MySQL and PostgreSQL...

Making Temporal Databases Work. Part 2: Computing Aggregates Across Temporal Versions

The temporal database is a database that can keep information on time when the facts represented in the database were,...

About the author

Lee is a 20 year industry veteran who has worked with IBM, AT&T, and many other firms. He holds a degree in physics, and more than 30 software-related patents. He currently works as a project consultant, specializing in database design and optimization. He can be reached at [email protected].

Lee's contributions

• T-SQL Programming
• Database Administration

Lee's latest contributions:

Partitioning in PostgreSQL was for many years a feature almost entirely ignored, and even today is often thought of only as a means to increase...

Justin Steele’s setback puts the Chicago Cubs into problem-solving mode again

CHICAGO — Craig Counsell stood up and exited the Wrigley Field interview room so quickly that reporters didn’t have enough time and presence of mind to compute the announcement and ask the Chicago Cubs manager any follow-up questions.

“ Kyle Hendricks is going to be the starter tomorrow,” Counsell said after Monday night’s 5-3 loss to the Pittsburgh Pirates . “We’re going to scratch Justin Steele . He’s experiencing some elbow soreness.”

Advertisement

The Cubs had been rolling to such an extent that the blown save and the postgame news became a disorienting combination. Jorge López , a dominant reliever for the past two months, was sitting at his locker and waiting for the media after giving up a game-tying, three-run homer to Bryan Reynolds and then the go-ahead home run to Andrew McCutchen in the eighth inning. Pittsburgh’s flurry snapped a six-game winning streak, part of a run when the Cubs won 12 of 15 and appeared to be peaking at the right time.

Those long-shot odds to reach the postseason became more remote with the uncertainty surrounding Steele, the homegrown lefty who has a 3.28 ERA in 85 career starts for the Cubs and a confidence that can be felt by the entire team. There goes what was supposed to be Tuesday’s marquee matchup at the Friendly Confines: Steele vs. Paul Skenes .

“Steele’s been awesome the last couple years here,” Cubs pitcher Jameson Taillon said after throwing seven scoreless innings in the Labor Day loss. “When you see Steele on the mound, you know you’re going to get a good effort and you’re going to be in the game. It stinks. But from everything I heard, we’re hoping it’s just a quick thing. He means a lot to this group. That being said, we just got (Jordan) Wicks back, which is awesome. We’ll just continue to keep going and step up.”

This season will still be a failure if the Cubs don’t make the playoffs. And Counsell could still push for big changes this offseason. But a manager who has spent a lot of time in observational mode has noticed the group’s ability to solve problems.

With a few exceptions, Counsell largely inherited David Ross’ coaching staff after he decided to leave the Milwaukee Brewers for a five-year, $40 million contract. The Brewers rallied around Pat Murphy, Counsell’s longtime bench coach and college coach at Notre Dame, preventing a mass exodus and ensuring some continuity.

The Brewers have already spent 153 days in first place in a year when Cubs chairman Tom Ricketts showed up for spring training and said: “I don’t see any reason why we wouldn’t be favored for our division.” Ricketts also declared this should be the standard: “Any year we don’t make the postseason is a disappointment.”

The Cubs (71-67) still have two teams in front of them racing for the National League’s last wild-card spot. The Cubs trail the Atlanta Braves by 3 1/2  games. The New York Mets are now only a half-game behind the Braves. The Cubs would lose the head-to-head tiebreaker against both teams. The Cubs are also a different club than the one that already lost the season series against the Braves and Mets, showing significant signs of improvement and adaptability.

The only reliever remaining from Counsell’s Opening Day bullpen is Drew Smyly — and the Cubs put him on waivers last week to try to save money. Adbert Alzolay , Julian Merryweather , Yency Almonte and Luke Little are on the injured list. Héctor Neris and José Cuas were both designated for assignment. Mark Leiter Jr. was traded to the New York Yankees in a deadline deal that focused on 2025 and beyond.

Jed Hoyer’s front office kept accumulating “out-getters,” Counsell’s term for flexible pitchers who remain available and operate without overly defined roles. Tyson Miller , Porter Hodge and López — who has not allowed a run in 19 of his 22 outings with the Cubs after getting released by the Mets — are among the finds that have turned this bullpen into a strength.

A group of pitching coaches led by Tommy Hottovy have also helped make Shota Imanaga ’s rookie season a smashing success. Taillon remains consistent and dependable, showing why the Cubs invested $68 million in his future. Steele has solidly built off last year’s All-Star season, despite straining his hamstring on Opening Day and dealing with this newly disclosed elbow issue.

A group of hitting coaches led by Dustin Kelly have helped implement significant in-season swing adjustments for Pete Crow-Armstrong and Miguel Amaya , adding new dimensions to the bottom of the lineup. Center fielders and catchers with two-way skills are extremely difficult to find. During this second-half run, the Cubs may have confirmed that they already have both of those rare types of players.

“Like the players, it’s staying in the fight,” Counsell said. “It’s staying with it. Keep coaching. Because things can turn. We don’t get to (pick when). You just don’t predict it. In the marathon of a season, there is time. Even though you can say there’s not, there’s time. So I think we’ve done a really good job of continuing the coaching and continuing the teaching. And not letting something just be the way it is and (saying): ‘We can’t figure this out.’

“We’ve stayed in the fight in that way. And then the players have stayed in the fight. It’s good to get rewarded with some positive results. That kind of validates your work. And I think, in the coaching staff’s case, it certainly has validated some of their work.”

That work, however, never feels done in early September. With or without Steele, the Cubs again have to regroup.

“We’re chasing — that in itself creates urgency,” Counsell said. “We know we have to play at a really high level because we’ve got significant ground still to make up. But we’ve also, in a sense, earned this. We’ve earned the right to have some fun this month and play some good baseball and see what happens.”

(Photo: Patrick Gorski / USA Today)

Get all-access to exclusive stories.

Subscribe to The Athletic for in-depth coverage of your favorite players, teams, leagues and clubs. Try a week on us.

Patrick Mooney is a senior writer for The Athletic covering the Chicago Cubs and Major League Baseball. He spent eight seasons covering the Cubs across multiple platforms for NBC Sports Chicago/Comcast SportsNet, beginning in 2010. He has been a frequent contributor to MLB Network, Baseball America, MLB.com and the Chicago Sun-Times News Group. Follow Patrick on Twitter @ PJ_Mooney