Query going to all paritions
Hi, Query on one of our partitioned tables which is range partitioned on "run"date" column is going to all partitions despite having run_date in WHERE clause. "enable_parition_pruning" is also on. I am unable to generate a query plan as the query never runs fully even waiting for say half an hour. We have composite indexes on run_date,status. Do I need to create an index on run_date only? Any other solutions? Regards, Aditya.
Re: Query going to all paritions
On Fri, 2021-10-01 at 12:58 +0530, aditya desai wrote: > Hi, > Query on one of our partitioned tables which is range partitioned on > "run"date" column is going to all partitions despite having run_date in WHERE > clause. "enable_parition_pruning" is also on. I am > unable to generate a query plan as the query never runs fully even waiting > for say half an hour. > > We have composite indexes on run_date,status. Do I need to create an index on > run_date only? You need to share the query and probably the table definition. EXPLAIN output (without ANALYZE) will also help. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Query going to all paritions
On Fri, Oct 01, 2021 at 02:24:11PM +0530, aditya desai wrote: > Hi Laurenz, > Please find attached explain query plan and query. Can you show us \d of the table, and exact query you ran? Also, please, don't send images. This is text, so you can copy-paste it directly into mail. Or, put it on some paste site - for explains, I suggest https://explain.depesz.com/ It's impossible to select text from image. It's much harder to read (it doesn't help that it's not even screenshot, but, what looks like, a photo of screen ?!
Re: Query going to all paritions
Will try to get a query in text format. It looks difficult though. Regards, Aditya. On Fri, Oct 1, 2021 at 4:03 PM hubert depesz lubaczewski wrote: > On Fri, Oct 01, 2021 at 02:24:11PM +0530, aditya desai wrote: > > Hi Laurenz, > > Please find attached explain query plan and query. > > Can you show us \d of the table, and exact query you ran? > > Also, please, don't send images. This is text, so you can copy-paste it > directly into mail. > > Or, put it on some paste site - for explains, I suggest > https://explain.depesz.com/ > > It's impossible to select text from image. It's much harder to read (it > doesn't help that it's not even screenshot, but, what looks like, > a photo of screen ?! >
Better, consistent instrumentation for postgreSQL using a similar API as Oracle
TLDR; If I spend the time necessary to instrument the many functions that are the equivalent of the Oracle counterparts, would anyone pull those changes and use them? Specifically, for those who know Oracle, I'm talking about implementing: 1. The portion of the ALTER SESSION that enables extended SQL trace 2. Most of the DBMS_MONITOR and DBMS_APPLICATION_INFO packages 3. Instrument the thousand or so functions that are the equivalent of those found in Oracle's V$EVENT_NAME 4. Dynamic performance view V$DIAG_INFO For the last 35 years, I've made my living helping people solve Oracle performance problems by looking at it, which means: Trace a user experience and profile the trace file to (a) reveal where the time has gone and its algorithm and (b) make it easy to imagine the cost of possible solutions as well as the savings in response time or resources. I've even submitted change requests to improve Oracle's tracing features while working for them and since those glorious five years. Now looking closely at postgreSQL, I see an opportunity to more quickly implement Oracle's current feature list. I've come to this point because I see many roadblocks for users who want to see a detailed "receipt" for their response time. The biggest roadblock is that without a *lot* of automation, a user of any kind must log into the server and attempt to get the data that are now traditionally child's play for Oracle. The second biggest roadblock I see is the recompilation that is required for the server components (i.e., postgreSQL, operating system). My initial attempts to get anything useful out of postgreSQL were dismal failures and I think it should be infinitely easier. Running either dtrace and eBPF scripts on the server should not be required. The instrumentation and the code being instrumented should be tightly coupled. Doing so will allow *anyone* on *any* platform for *any* PostgreSQL version to get a trace file just as easily as people do for Oracle.
