Using a database index for report performance 

Consider indexing a table column that is used as a filter criterion in the SQL query.

To complete the assignment, accomplish the following tasks.

1. Download the address data from the following link: Excercise.zip (Openaddr Collected US Northeast File). 
2. Transfer the .zip file to the $/home/architect/Desktop directory.                                                                                          
3. Load the .csv address data into the PostgreSQL database by using the query tool of pgAdmin4.
4. In the Exercise file, open .sql file fsg_data_address_import.sql and execute.
5. Execute the following query to verify that all the records are loaded successfully.                                   

   Select count(*) from customerdata.fsg_data_address

                                                         
6. Instead of modifying the Haversine formula by adding a filter criterion such as AND street LIKE '1 ROGERS STR%', perform a simple query within pgAdmin4 against the address table to analyze statistics. The following table is an example.                                                 

   EXPLAIN (ANALYZE true, COSTS true, FORMAT JSON) SELECT pyguid AS pyGUID, reference AS Reference, isfor AS IsFor, street AS Street, city AS City, state AS State, postalcode AS PostalCode, country AS Country, latitude AS Latitude, longitude AS Longitude FROM customerdata.fsg_data_address WHERE street like '1 ROGERS STR%';

    
7. Verify and capture the statistics for the sequential scan.                                                                                                    
8. Under pr_fsg_data_address, select Indexes. 
9. Right-click, and then select Create.
10. Provide an index name such as idx_street or StreetIDX.
11. In the Definition table, select btree.
12. Add the street column to the index.
13. Choose any operator class, such as text_pattern_ops.                  
14. Repeat step 6 to see significantly faster performance due to an Index Scan being used.                                                                                                                                                                                                                   
15. Optional: Execute the following modified version of the Haversine formula query.

    EXPLAIN (ANALYZE true, COSTS true, FORMAT json) SELECT pyGUID AS pyGUID, Reference AS Reference, IsFor AS IsFor, Street AS Street, City AS City, State AS State, PostalCode AS PostalCode, Country AS Country, Latitude AS Latitude, Longitude AS Longitude, Distance AS Distance FROM ( SELECT z.pyguid AS pyGUID, z.reference AS Reference, z.isfor AS IsFor, z.street AS Street, z.city AS City, z.state AS State, z.postalcode AS PostalCode, z.country AS Country, z.latitude AS Latitude, z.longitude AS Longitude, p.radius, p.distanceunit * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(z.latitude)) * COS(RADIANS(p.longpoint - z.longitude)) + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(z.latitude)))) AS Distance FROM customerdata.fsg_data_address AS z JOIN ( SELECT 42.0 AS latpoint, -71.0 AS longpoint, 30.0 AS radius, 69.0 AS distanceunit ) AS p ON 1 = 1 ) AS d WHERE distance <= radius AND street LIKE '1 ROGERS STR%' ORDER BY distance  The following image depicts the explain statement execution of the modified version of the Haversine formula. Note: Attempting to index the latitude and longitude columns does not improve performance because these columns are numeric. pgAdmin4 does not support the definition of indexes for numeric columns. Forcing an index on a numeric column using DDL syntax, for example, CREATE INDEX idx_lat ON customerdata.fsg_data_address(latitude), does not work; the index is ignored.

      
16. Verify that indexed columns result in significantly improved performance readings.