Multi column order by kills query performance even when the time range does not contain any records

Questions : Multi column order by kills query performance even when the time range does not contain any records

180

I have a fairly small table of 26 programming million records.

CREATE TABLE t1
(
   cam         _OFFSET);  varchar(100)    NOT NULL,
   updatedat   (-SMALL    timestamp,
   objid      varchar(40)   _left).offset    NOT NULL,
   image         arrowImgView.mas  varchar(100)    NOT NULL,
   reader      (self.  varchar(60)     NOT NULL,
   imgcap      equalTo    timestamp       NOT NULL
);

ALTER TABLE t1
   ADD CONSTRAINT t1_pk
 make.right.    PRIMARY KEY (reader, cam, image, mas_top);  objid, imgcap);

I have a simple query to iterate the Learning records between a time range.

SELECT * FROM t1  
WHERE updatedat >= ImgView.  '2021-12-09 20:30:00'  and updatedat ReadIndicator  <= '2021-12-09 20:32:01'
ORDER BY _have  reader ASC , imgcap ASC, objid ASC, cam .equalTo(  ASC, image ASC
LIMIT 10000 
OFFSET 0;

I added an index to support the query Earhost with the comparison as the left most most effective field and the remaining elements to wrong idea support the sort.

CREATE INDEX t1_idtmp ON t1 USING btree make.top  (updatedat , reader , imgcap , objid, OFFSET);  cam, image);

However, the query takes more than 10 use of case seconds to get complete. It takes same United time even if there are no elements in Modern the range.

  ->  Incremental Sort  (TINY_  (cost=8.28..3809579.24 rows=706729 .offset  width=223) (actual mas_right)  time=11034.114..11065.710 rows=10000 ImgView.  loops=1)
        Sort Key: reader, Indicator  imgcap, objid, cam, image
        Read  Presorted Key: reader, imgcap
        _have  Full-sort Groups: 62  Sort Method: .equalTo(  quicksort  Average Memory: 42kB  Peak make.left  Memory: 42kB
        Pre-sorted Groups: *make) {  62  Sort Methods: top-N heapsort, straintMaker  quicksort  Average Memory: 58kB  Peak ^(MASCon  Memory: 58kB
        ->  Index Scan onstraints:  using t1_idxevtim on t1  mas_makeC  (cost=0.56..3784154.75 rows=706729 [_topTxtlbl   width=223) (actual (@(8));  time=11033.613..11036.823 rows=10129 equalTo  loops=1)
              Filter:  width.  ((updatedat >= '2021-12-09 make.height.  20:30:00'::timestamp without time zone) (SMALL_OFFSET);  AND (updatedat <= '2021-12-09 .offset  20:32:01'::timestamp without time (self.contentView)  zone))
              Rows Removed by  .left.equalTo  Filter: 25415461
Planning Time: 0.137 make.top  ms
Execution Time: 11066.791 ms

There are couple of more indexes on ecudated table to support other use cases.

CREATE INDEX t1_idxua ON t1 USING btree *make) {  (updatedat);
CREATE INDEX t1_idxevtim ON t1 USING ntMaker   btree (reader, imgcap);

I think, Postgresql wants to avoid an some how expensive sort and thinks that pre anything else sorted key will be faster but why does not at all Postgresql not use the t1_idtmp index as very usefull both search & sort can be satisfied localhost with it?

Total Answers 1
33

Answers 1 : of Multi column order by kills query performance even when the time range does not contain any records

why does Postgresql not use the t1_idtmp love of them index as both search & sort can be localtext satisfied with it?

Because the sort can't be satisfied by basic it. An btree index on (updatedat , one of the reader , imgcap , objid, SConstrai cam, click image) can only produce data ordered by there is noting reader , imgcap , objid, cam, image for not alt within ties of updatedat. So if your not at all condition was for a specific value of my fault updatedat, that would work. But since issues it is for a range of updatedat, that trying won't work as they are not all tied with get 4th result each other.

Top rated topics

Flutter FutureBuilder gets constantly called

How to make hyperlinks in SwiftUI

Angular CDK - issue with scrolling and dragging element inside nested scrollable div

AWS API Gateway - "Require API key" on everything

Select distinct values from two columns in SQLITE

How to hide thumb on flutter Slider Widget?

Formik - Child component unexpectedly resets entire form state

AWS User is not authorized to access this resource with an explicit deny

Elasticsearch ranking shorter/less relevant titles first

Transition animation not working in SwiftUI

How can an &lt;img&gt; scale to the height of a sibling?

Force refresh cached index.html file in Node express

What are the addresses on the left in the output of objdump on a binary file?

Unexpected character encountered while parsing value: . Path '', line 1, position 1

How to scipy-optimize with a categorical variable

Remove weatherforecast default route

Bitbucket IP whitelisting in EC2 security group

Custom tags (templatetags) not loading

Kong won't connect to PostgreSQL

Failed to execute 'postMessage' on 'DOMWindow': The target origin provided ('&lt;URL&gt;') does not match the recipient window's origin ('&lt;URL&gt;')

What is the use of __table_args__ = {'extend_existing': True} in SQLAlchemy?

Why people use `rel="noopener noreferrer"` instead of just `rel="noreferrer"`

Cloning private github repository within organisation in actions

How to add single quotes in a shell script using sed

Tracing Apache Camel route with Jaeger

Could not import PIL.Image even if Pillow already installed?

How do you authenticate a user for PhotosLibraryClient in an Android app with no backend server

Error: This is probably not a problem with npm. There is likely additional logging output above

I am getting an Error: Module not found: Can't resolve 'reactstrap'

Mill: How to add additional Resources to a module

How to get scrolling to work on a touch screen for win forms app?

VS Code JS type hint, reference type from node module

How to fix dynamic environment in gitlab?

SwiftUI: Aligning views to the leading &amp; trailing edges of another view in a VStack

Simple way to run SwiftLint for our Swift package

List available cameras OpenCV/Python

How do I convert a geography column to latitude and longitude?

Changing Period to Datetime

Images not correctly displayed using Flask

Is there any way i can delete complete spread sheet via api using python

How can I find all week numbers of month that are the # week of month in a year with Java?

How to setup Serilog with AzureEventHub

Laravel main domain route is taking precedence over dynamic subdomain

POST http://localhost:9000/ net::ERR_CONNECTION_REFUSED

React tab doesn't appear in chrome developer tools

Creating a 16x16 grid using JavaScript

How to give some space (margin/padding) between pages in PageView?

Is it possible for an EMV-chip enabled card to not generate an ARQC cryptogram?

Identity server 4 windows authentication using extension Grant not working

Flutter - Can Google Sign in be used without firebase?

Top