Most application developers, ORMs, and charting tools like Grafana or Tableau will still use the simpler, straight-forward form: SELECT DISTINCT ON (tags_id) * FROM cpu (SELECT tags_id FROM cpu ORDER BY tags_id, time DESC LIMIT 1)īut even if writing a RECURSIVE CTE like this in day-to-day querying felt natural to you, there's a bigger problem. However, if you're an experienced PostgreSQL user, you might point out that it is already possible to get reasonably fast DISTINCTqueries via RECURSIVE CTEs.įrom the PostgreSQL Wiki, using a RECURSIVE CTE can get you good results, but writing these kinds of queries can often feel cumbersome and unintuitive, especially for developers new to PostgreSQL: WITH RECURSIVE cte AS ( Optimizing DISTINCT query performance: What about RECURSIVE CTEs? And our blog post on using pg_stat_statements to optimize queries.This beginner's guide to EXPLAIN ANALYZEby Michael Christofides in one of our Timescale Community Days.If you're new to PostgreSQL and are wondering how to check your query performance in the first place (and optimize it!), we're going to leave two helpful resources here: ![]() ![]() How to check (and optimize) your query performance in PostgreSQL Skip Scan makes Timescale better and PostgreSQL a better, more competitive database overall, especially compared to MySQL, Oracle, DB2, and others. We constantly seek to advance the state of the art with databases, and features like Skip Scan are only our latest contribution to the industry. PostgreSQL is the world’s fastest-growing database, and we are excited to support it alongside thousands of other users and contributors. We contribute to the ecosystem around PostgreSQL. We employ engineers who contribute to PostgreSQL. Developers who use Timescale benefit from a purpose-built time-series database plus a classic relational (Postgres) database, all in one, with full SQL support.Īnd to be clear, we love PostgreSQL. It’s a relational database, specifically, a relational database for time series. This is because Timescale is not just a time-series database. This means that with Timescale, not only will your time-series DISTINCT queries be faster, but any other related queries you may have on normal PostgreSQL tables will also be faster. This feature works in both Timescale hypertables and distributed hypertables, and normal PostgreSQL tables. Today, via TimescaleDB 2.2.1, we are releasing TimescaleDB Skip Scan, a custom query planner node that makes ordered DISTINCT queries blazing fast in PostgreSQL □.Īs you'll see in the benchmarks below, some queries performed more than 8,000x better than before-and many of the SQL queries your applications and analytics tools use could also see dramatic improvements with this new feature. We don’t want our users to have to wait that long. Unfortunately, this patch wasn't included in the CommitFest for PostgreSQL 14, so it won't be included until PostgreSQL 15 at the earliest (i.e., no sooner than Fall 2022, at least 1.5 years from now). (Note: We couldn’t use this implementation directly due to some limitations of what is possible within the Postgres extension framework.) Since 2018, there have been plans to support something similar in PostgreSQL. Without support for this feature, the database engine has to scan the entire ordered index and then deduplicate it at the end-which is a much slower process. When a database has a feature like "Skip Scan," it can incrementally jump from one ordered value to the next without reading all of the rows in between. Other databases like MySQL, Oracle, and DB2 implement a feature called "Loose indexscan," "Index Skip Scan," or “Skip Scan” to speed up the performance of queries like this. As a table grows (and they grow quickly with time-series data), this operation keeps getting slower. Why are DISTINCT queries slow on PostgreSQL when they seem to ask an "easy" question? It turns out that PostgreSQL currently lacks the ability to efficiently pull a list of unique values from an ordered index.Įven when you have an index that matches the exact order and columns for these "last-point" queries, PostgreSQL is still forced to scan the entire index to find all unique values. ![]() Waiting for our DISTINCT queries to return ![]() PostgreSQL is an amazing database, but it can struggle with certain types of queries, especially as tables approach tens and hundreds of millions of rows (or more).
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |