How to read or observe reduction in query execution time from Query Plan?-CodePudding

I was reviewing how to set up simple B-Tree indexes, the most common type of index. In tutorial they’ve loaded some open weather data with data events by type, damage, time and location. For just a very basic index, I followed this given tutorial on given link https://www.crunchydata.com/developers/playground/btree-indexes Theoratically I know that B-Tree indexes reduce query execution time but i'm not sure how to observe this in Query Plan generated.

Query Plan Before Creating B-tree Index: Query Plan - 1

Query Plan After Creating B-tree Index: Query Plan - 2

CodePudding user response：

Yes, you can see the query plan generated by PostgreSQL by using the "EXPLAIN" statement or some monitoring tools. The output of EXPLAIN shows the execution plan for the query, including the sequence of operations used to retrieve the data, the estimated number of rows processed, and the estimated time required to execute the query.

You can use the EXPLAIN statement before the query that you want to analyze. For example:

Explain
SELECT * FROM weather
WHERE event_type = 'Winter Storm';

You can also use the EXPLAIN ANALYZE statement, which will not only show the execution plan but also execute the query and show the actual execution time. For example:

Explain ANALYZE
SELECT * FROM weather
WHERE event_type = 'Winter Storm';

You can compare multiple parameters before and after creating B-tree or any other indexes for example,

Type of scan: that is used before and after, (Seq Scan vs Index Scan).
Actual Time": monitor the actual time taken by the query to execute by using tools like pgAdmin, pg_stat_statements, and other monitoring tools.
Execution Time: of the query displayed in the output of the EXPLAIN statement. By comparing the execution time of a query before and after optimization, you can see if the query is running faster.
Total Cost: of the query, as estimated by the query optimizer, is also displayed in the output of the EXPLAIN statement. A lower total cost indicates that the query is expected to run faster.

You can also use monitoring tools such as pgAdmin, a popular open-source administration, and management tool for PostgreSQL, which has the feature to visualize the execution plan of a query. some other visualization tools are: pganalyze, pg_statsinfo, Postgresql explain visualizer and QueryPie.

Thumbs up if you found it useful!!