CoderFunda
  • Home
  • About us
    • Contact Us
    • Disclaimer
    • Privacy Policy
    • About us
  • Home
  • Php
  • HTML
  • CSS
  • JavaScript
    • JavaScript
    • Jquery
    • JqueryUI
    • Stock
  • SQL
  • Vue.Js
  • Python
  • Wordpress
  • C++
    • C++
    • C
  • Laravel
    • Laravel
      • Overview
      • Namespaces
      • Middleware
      • Routing
      • Configuration
      • Application Structure
      • Installation
    • Overview
  • DBMS
    • DBMS
      • PL/SQL
      • SQLite
      • MongoDB
      • Cassandra
      • MySQL
      • Oracle
      • CouchDB
      • Neo4j
      • DB2
      • Quiz
    • Overview
  • Entertainment
    • TV Series Update
    • Movie Review
    • Movie Review
  • More
    • Vue. Js
    • Php Question
    • Php Interview Question
    • Laravel Interview Question
    • SQL Interview Question
    • IAS Interview Question
    • PCS Interview Question
    • Technology
    • Other

08 September, 2024

Why Postgres query is not using column in Index Condition but uses it in Filter condition

 Programing Coderfunda     September 08, 2024     No comments   

I am running a simple join query, where the table incident_tracker_details is covered by a suitable index. The query is slow because it doesn`t use all the columns in Index Condition, but does use the column as a Filter leading to a large heap searches


Query:
SELECT
relevant_incidents_per_inventory_item.inventory_id,
tracker_id
FROM
relevant_incidents_per_inventory_item
JOIN
incident_tracker_details
ON
incident_tracker_details.incident_id = relevant_incidents_per_inventory_item.incident_id
WHERE
incident_tracker_details.company_id = :companyId
and incident_tracker_details.division_id = :divisionId
AND incident_tracker_details.value > 0



the index in use:
create index companyid_divisionId_incidentId_idx
on incident_tracker_details (company_id, division_id, incident_id)
where (value > 0) include (tracker_id);



the relevant_incidents_per_inventory_item does not have any relevant indexes and the table is very small (few hundreds of rows).


the actual query plan:
QUERY PLAN
Hash Join (cost=6901.58..6913.72 rows=1 width=8) (actual time=90.663..315.278 rows=522 loops=1)
Hash Cond: (incident_tracker_details.incident_id = relevant_incidents_per_inventory_item.incident_id)
-> Index Only Scan using companyid_divisionId_incidentId_idx on incident_tracker_details (cost=0.57..12.68 rows=6 width=16) (actual time=0.039..202.787 rows=246774 loops=1)
Index Cond: ((company_id = '777704491747470679'::bigint) AND (division_id = '777795770460846005'::bigint))
Heap Fetches: 124039
-> Hash (cost=6900.88..6900.88 rows=10 width=8) (actual time=86.704..86.709 rows=259 loops=1)
...not relevant part
Planning Time: 0.518 ms
Execution Time: 315.402 ms



Why is the "incident_id" not part of the Index Cond? this leads to retrieving all the rows under companyId, divisionId , and then the engine has to filter all the rows for those having incident_tracker_details.incident_id = relevant_incidents_per_inventory_item.incident_id


I expected to see all the columns used in the Index Condition, leading to fast retrieval of only the relevant data
  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to XShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Thanks

Meta

Popular Posts

  • Writing and debugging Eloquent queries with Tinkerwell
    In this article, let's look into the options that you can use with Tinkerwell to write and debug Eloquent queries easier. The post Wr...
  • The token request was rejected by the remote server
    error:invalid_granterror_description:The token request was rejected by the remote server.error_uri: https://documentation.openiddict.com/err...
  • Sitaare Zameen Par Full Movie Review
     Here’s a  complete Vue.js tutorial for beginners to master level , structured in a progressive and simple way. It covers all essential topi...
  • Vue.js Tutorial
      Vue.js Installation Compatibility Check Before going to install and use Vue.js in your project, you should check the compatibility issues....
  • JqueryUI Tutorial
    JqueryUI Tutorial    JqueryUI is the most popular front end frameworks currently. It is sleek, intuitive, and powerful mobile first fr...

Categories

  • Ajax (26)
  • Bootstrap (30)
  • DBMS (42)
  • HTML (12)
  • HTML5 (45)
  • JavaScript (10)
  • Jquery (34)
  • Jquery UI (2)
  • JqueryUI (32)
  • Laravel (1017)
  • Laravel Tutorials (23)
  • Laravel-Question (6)
  • Magento (9)
  • Magento 2 (95)
  • MariaDB (1)
  • MySql Tutorial (2)
  • PHP-Interview-Questions (3)
  • Php Question (13)
  • Python (36)
  • RDBMS (13)
  • SQL Tutorial (79)
  • Vue.js Tutorial (69)
  • Wordpress (150)
  • Wordpress Theme (3)
  • codeigniter (108)
  • oops (4)
  • php (853)

Social Media Links

  • Follow on Twitter
  • Like on Facebook
  • Subscribe on Youtube
  • Follow on Instagram

Pages

  • Home
  • Contact Us
  • Privacy Policy
  • About us

Blog Archive

  • July (4)
  • September (100)
  • August (50)
  • July (56)
  • June (46)
  • May (59)
  • April (50)
  • March (60)
  • February (42)
  • January (53)
  • December (58)
  • November (61)
  • October (39)
  • September (36)
  • August (36)
  • July (34)
  • June (34)
  • May (36)
  • April (29)
  • March (82)
  • February (1)
  • January (8)
  • December (14)
  • November (41)
  • October (13)
  • September (5)
  • August (48)
  • July (9)
  • June (6)
  • May (119)
  • April (259)
  • March (122)
  • February (368)
  • January (33)
  • October (2)
  • July (11)
  • June (29)
  • May (25)
  • April (168)
  • March (93)
  • February (60)
  • January (28)
  • December (195)
  • November (24)
  • October (40)
  • September (55)
  • August (6)
  • July (48)
  • May (2)
  • January (2)
  • July (6)
  • June (6)
  • February (17)
  • January (69)
  • December (122)
  • November (56)
  • October (92)
  • September (76)
  • August (6)

Loading...

Laravel News

Loading...

Copyright © CoderFunda | Powered by Blogger
Design by Coderfunda | Blogger Theme by Coderfunda | Distributed By Coderfunda