Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.probalytics.io/llms.txt

Use this file to discover all available pages before exploring further.

Performance tips and common mistakes to avoid.

​
Filter by ORDER BY Fields

Tables are sorted by their ORDER BY columns. Filtering on these makes queries much faster. markets: ORDER BY (platform, created_at, id, platform_id) fills: ORDER BY (platform, market_id, timestamp, id) orderbook_snapshots: ORDER BY (market_id, outcome.id, timestamp) Always filter by platform and time columns when possible.

​
Examples

-- Slow: No ORDER BY filters
SELECT * FROM markets WHERE title LIKE '%election%';

-- Fast: Filter by ORDER BY columns first
SELECT * FROM markets
WHERE platform = 'POLYMARKET'
  AND created_at >= '2024-01-01'
  AND title LIKE '%election%';

-- Slow: Scans all fills
SELECT * FROM fills WHERE price > 0.9;

-- Fast: Filter by ORDER BY columns
SELECT * FROM fills
WHERE platform = 'POLYMARKET'
  AND market_id = 'your-market-uuid-here'
  AND timestamp >= '2024-01-01'
  AND price > 0.9;

​
Common Mistakes

​
Always Use LIMIT

Avoid loading millions of rows unintentionally—it’s slow and wastes resources. 
-- Bad: Loads massive result set
SELECT * FROM fills;

-- Good: Quick exploration
SELECT * FROM fills LIMIT 100;

​
Don’t Overuse FINAL

FINAL forces immediate deduplication and is significantly slower. Use it only for counts/aggregations where exact results matter. 
-- Slow: FINAL for exploration
SELECT * FROM markets FINAL LIMIT 100;

-- Fast: Skip FINAL for data browsing
SELECT * FROM markets LIMIT 100;

-- Use FINAL for accurate counts
SELECT count() FROM markets FINAL;

​
Select Only What You Need

-- Bad: Transfers 20+ columns unnecessarily
SELECT * FROM fills;

-- Good: Only needed columns
SELECT timestamp, normalized_price, size, taker_side FROM fills;

​
Tier-Specific Tips

​
Starter Tier

(30 days data, 100K row limit, 100 queries/hr)
  • Always filter by date: timestamp >= now() - INTERVAL 7 DAY
  • Keep result sets under 100K rows
  • Use SAMPLE for approximate results:
SELECT avg(normalized_price)
FROM fills SAMPLE 0.1
WHERE timestamp >= now() - INTERVAL 30 DAY;

​
Pro & Custom Tiers

  • Leverage full historical data
  • Still filter by ORDER BY columns for best performance

​
Quick Tips

Percentiles 
SELECT quantile(0.95)(normalized_price) as p95_price FROM fills;
Cardinality estimates 
SELECT uniq(taker_id) as unique_takers FROM fills;
Unnest outcomes array 
SELECT outcome.name
FROM markets
ARRAY JOIN outcomes AS outcome
WHERE platform = 'POLYMARKET';
Best bid/ask from orderbook 
SELECT
    bids[1].price as best_bid,
    asks[1].price as best_ask,
    asks[1].price - bids[1].price as spread
FROM orderbook_snapshots
WHERE market_id = 'your-market-uuid-here'
ORDER BY timestamp DESC
LIMIT 1;