Basic PostgreSQL

1.txt

// 1. Create table
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  amount DECIMAL(10,2)
);


// 2. Generate test data (100,000 records)
INSERT INTO orders (customer_id, order_date, amount)
SELECT 
  FLOOR(RANDOM() * 1000)::INT, 
  NOW() - (FLOOR(RANDOM() * 1000) || ' days')::INTERVAL,
  ROUND(cast(RANDOM() * 1000 AS numeric), 2)
FROM generate_series(1, 100000);

// 3. Explain query
EXPLAIN ANALYZE
SELECT * FROM orders
ORDER BY order_date DESC;

// 4. Create index
CREATE INDEX idx_order_date ON orders(order_date DESC);

// 5. Working with paging
EXPLAIN ANALYZE
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 10 OFFSET 100;

// 6. List indexs
SELECT * FROM pg_indexes WHERE tablename = 'orders';