2) Optimizing Postgres Databases

1) Introduction

A) Exploring Postgres Internal
B) Debugging Postgres Queries
C) Using an Index
D) Advanced Indexing
E) Vacuuming Postgres Databases

A1) Exploring Postgres Internals

import psycopg2

conn = psycopg2.connect(dbname="dq", user="hud_admin", password="eRqg123EEkl")
print(conn)

A2) Investigating the Tables

conn = psycopg2.connect(dbname="dq", user="hud_admin", password="eRqg123EEkl")
cur = conn.cursor()
conn = psycopg2.connect(dbname="dq", user="hud_admin", password="eRqg123EEkl")
cur = conn.cursor()

cur.execute("SELECT table_name FROM information_schema.tables ORDER BY table_name")
table_names = cur.fetchall()
for name in table_names:
    print(name)
    
    
    
# Instructions:
Use the provided cur object.
Using the SELECT query, grab the table_name column from the information_schema.tables table with the ORDER BY option on the table_name column.
Fetch all the results and assign them to the variable table_names.
Loop through table_names:
Print each table_name from the query.

A3) Working with Schemas

conn = psycopg2.connect(dbname="dq", user="hud_admin", password="eRqg123EEkl")
cur = conn.cursor()
conn = psycopg2.connect(dbname="dq", user="hud_admin", password="eRqg123EEkl")
cur = conn.cursor()

cur.execute("SELECT table_name FROM information_schema.tables WHERE table_schema='public' ORDER BY table_name")
for table_name in cur.fetchall():
    name = table_name[0]
    print(name)





# Instructions:
Use the provided cur object.
Using the SELECT query, grab the table_name column from the information_schema.tables table.
Find user created by filtering the query on the table_schema column.
ORDER BY the table_name again.
Loop through cur.fetchall() and print each table_name from the query.

A4) Describing the Tables

conn = psycopg2.connect(dbname="dq", user="hud_admin", password="eRqg123EEkl")
cur = conn.cursor()

cur.execute("SELECT table_name FROM information_schema.tables WHERE table_schema='public'")
#for table in cur.fetchall(): 
    # Enter your code here...
from psycopg2.extensions import AsIs

conn = psycopg2.connect(dbname="dq", user="hud_admin", password="eRqg123EEkl")
cur = conn.cursor()

cur.execute("SELECT table_name FROM information_schema.tables WHERE table_schema='public'")
for table in cur.fetchall():
    table = table[0]
    cur.execute("SELECT * FROM %s LIMIT 0", [AsIs(table)])
    print(cur.description, "\n")



# Instructions
Import AsIs from psycopg2.extensions.
Within the loop for cur.fetchall() for each table name:
Run a SELECT query with the table variable using AsIs.
Print the cur.description attribute.
Print a black space to seperate the descriptions at the end of each loop.

A5) Type Code Mappings

conn = psycopg2.connect(dbname="dq", user="hud_admin", password="eRqg123EEkl")
cur = conn.cursor()
conn = psycopg2.connect(dbname="dq", user="hud_admin", password="eRqg123EEkl")
cur = conn.cursor()

cur.execute("SELECT oid, typname FROM pg_catalog.pg_type")
type_mappings = {
    int(oid): typname
    for oid, typname in cur.fetchall()
}



# Instructions:
Use the provided cur object.
Using execute(), SELECT from the pg_catalog.pg_type, choose two columns that can map an integer type code to a human readable string.
Create a dict and assign it to the variable type_mappings.
Loop through the returned SELECT query and map the integer type code to the string. It should look something similar to this:
type_mappings = {
    16: 'bool',
    18: 'char',
    19: 'name',
    ...
}

A6) Readable Description Types

from psycopg2.extensions import AsIs
conn = psycopg2.connect(dbname="dq", user="hud_admin", password="eRqg123EEkl")
cur = conn.cursor()
# You have `table_names` and `type_mappings` provided for you.
conn = psycopg2.connect(dbname="dq", user="hud_admin", password="eRqg123EEkl")
cur = conn.cursor()

readable_description = {}
for table in table_names:
    cur.execute("SELECT * FROM %s LIMIT 0", [AsIs(table)])
    readable_description[table] = dict(
        columns=[
            dict(
                name=col.name,
                type=type_mappings[col.type_code],
                length=col.internal_size
            )
            for col in cur.description
        ]
    )
print(readable_description)





# Instructions:
Let's put all this together and create our own table descriptions. We want to rewrite the description attributes from a list of tuples towards something human readable. In the following exercise, we will assemble output from the previous exercises into this dictionary:


{
    "homeless_by_coc":
        {
            columns: [
                {
                    name: "id"
                    type: "int4"
                    internal_size: 4
                },
                {
                    name: "year",
                    type: "date",
                    internal_size: 4
                },
                {
                    name: "state",
                    type: "char",
                    internal_size: 2
                },
                {
                    name: "coc_number",
                    type: "char",
                    internal_size: 128
                },
                {
                    name: "measures",
                    type: "varchar",
                    internal_size: 64 
                },
                {
                    name: "count",
  Use the provided cur, type_mappings, and table_names objects.
Create a dict and assign it to the variable readable_description.
Loop through the table_names with the table variable and do the following:
Get the description attribute for the given table.
Map the name of the table to a dictionary with a columns key.
Recreate the columns list from the screen example by iterating through the description, and mapping the appropriate types.
Print the readable_description dictionary at the end.

A7) Number of Rows

from psycopg2.extensions import AsIs
conn = psycopg2.connect(dbname="dq", user="hud_admin", password="eRqg123EEkl")
cur = conn.cursor()
conn = psycopg2.connect(dbname="dq", user="hud_admin", password="eRqg123EEkl")
cur = conn.cursor()

for table in readable_description.keys():
    cur.execute("SELECT COUNT(*) FROM %s", [AsIs(table)])
    readable_description[table]["total"] = cur.fetchone()
    
    



# Instructions:
Use the provided cur object and AsIs class.
Loop through the readable_description keys:
Fetch the value of each table's row count and assign it to a total key for that table.
Print the readable_description dictionary at the end.

A8) Sample Rows

from psycopg2.extensions import AsIs
conn = psycopg2.connect(dbname="dq", user="hud_admin", password="eRqg123EEkl")
cur = conn.cursor()
conn = psycopg2.connect(dbname="dq", user="hud_admin", password="eRqg123EEkl")
cur = conn.cursor()

for table in readable_description.keys():
    cur.execute("SELECT * FROM %s LIMIT 100", [AsIs(table)])
    readable_description[table]["sample_rows"] = cur.fetchall()
    
    
    
    
    
#Instructions:
Use the provided cur object and AsIs class.
Loop through the readable_description keys and run the following:
Select the first 100 rows with SELECT ... LIMIT using execute() and AsIs.
Fetch the all the rows and assign it to the readable_description dictionary for the given table using the sample_rows key.
Print the readable_description dictionary at the end.

B1) The EXPLAIN query

import pprint as pp
conn = psycopg2.connect(dbname="dq", user="hud_admin", password="abc123")
cur = conn.cursor()
conn = psycopg2.connect(dbname="dq", user="hud_admin", password="abc123")
cur = conn.cursor()
cur.execute("EXPLAIN SELECT * FROM homeless_by_coc")
pp.pprint(cur.fetchall())



# Instructions:
Use the provided cur object.
Run the EXPLAIN command for a SELECT all query on the homeless_by_coc table.
Call .fetchall() and pretty print the output.

B2) The Path of a Query

conn = psycopg2.connect(dbname="dq", user="hud_admin", password="abc123")
cur = conn.cursor()
conn = psycopg2.connect(dbname="dq", user="hud_admin", password="abc123")
cur = conn.cursor()
cur.execute("EXPLAIN SELECT COUNT(*) FROM homeless_by_coc WHERE year > '2012-01-01'")
pp.pprint(cur.fetchall())


# Instructions:
Use the provided cur object.
Run the EXPLAIN command on a query that returns a COUNT of rows greater than the year 2012-01-01 for homeless_by_coc.
Call .fetchall() and pretty print the output.