Commonly Used SQL/Active Record/Database Snippets

DatabaseResources.md

Query Syntax

SELECT now() - query_start as "runtime", usename, datname, waiting, state, query
  FROM  pg_stat_activity
  WHERE now() - query_start > '2 minutes'::interval
 ORDER BY runtime DESC;

Migrations

Active Record first followed by PostrgeSQL equivalent

# Foreign Key

add_foreign_key :products, :product_groups
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

(http://edgeapi.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_index)
add_index(:suppliers, :name)
CREATE INDEX suppliers_name_index ON suppliers(name)

(http://apidock.com/rails/ActiveRecord/ConnectionAdapters/SchemaStatements/remove_index)
remove_index :suppliers, :name
DROP INDEX suppliers_name_index

Optimization Resources

• PostegrSQL Partitioning - Break table into chunks

• PostegrSQL Views - Cache dynamically built tables (see Scenic)

• PostgreSQL Query Documentation - Helpful Subpage

• PostgreSQL Indexing Documentation

  • All indexes in PostgreSQL are secondary indexes, meaning that each index is stored separately from the table's main data area (which is called the table's heap in PostgreSQL terminology)

  • Types

    • B-tree - Default
      • Good for equality and comparison operators to sort order (i.e. primary key, IS NULL)
      • Good for pattern matching operators LIKE only when anchored to beginning
    • Hash
      • Only handle simple equality comparisons
    • GiST
      • "nearest-neighbor" searches
      • infrastructure within which many different indexing strategies can be implemented
    • SP-GiST
      • permits implementation of a wide range of different non-balanced disk-based data structures, such as quadtrees, k-d trees, and radix trees (tries)
    • GIN
      • "inverted indexes"
      • Appropriate for data values that contain multiple component values, such as arrays
    • BRIN
      • Block Range INdexes
    • Muilticolumn indexes
      • Only the B-tree, GiST, GIN, and BRIN index types support multicolumn indexes
      • Max 32 columns
      • Most efficient when there are constraints on the leading (leftmost) columns
      • should be used sparingly
    • Indexes on Expressions
      • useful when retrieval speed is more important than insertion and update speed)
    • Partial Indexes
      • exclude uninteresting records from index
    • Index Only Scans
      • Don't grab from heap (the model's table), just index table

  • Scan Types

    • bitmap scan -
    • index - using index
    • seq scan - simple sequential scan

• PostegrSQL Subqueries Documentation

• PostegrSQL Unions - combine the results of two queries

• Queries Using With

Text Searches

• Like/Similar
• PostgreSQL Docs

EXPLAIN [ANALYZE] Tips

• The query optimizer takes LIMIT into account when generating query plans, so you are very likely to get different plans (yielding different row orders)
• Since no output rows are delivered to the client, network transmission costs and I/O conversion costs are not included
• You can turn off certain scan types (ex SET enable_seqscan TO off;)
• Seq Scan on tenk1 => (cost=0.00..458.00 rows=10000 width=244) (cost=STARTUPTIME..ESTIMATED_TIME_IN_MS, rows=RECORDS_COUNT width=WIDTH_OF_ROWS_IN_BYTES)