Skip to main content
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) trades: ORDER BY (platform, trader_id, created_at, id) Always filter by platform and created_at 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 trades
SELECT * FROM trades WHERE price > 0.9;

-- ✅ Fast: Filter by ORDER BY columns
SELECT * FROM trades
WHERE platform = 'Polymarket'
  AND created_at >= '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 trades;

-- ✅ Good: Quick exploration
SELECT * FROM trades 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 30+ columns unnecessarily
SELECT * FROM markets;

-- ✅ Good: Only needed columns
SELECT id, title, platform, created_at FROM markets;

​
Tier-Specific Tips

​
Starter Tier

(30 days data, 100K row limit, 100 queries/hr)
  • Always filter by date: created_at >= now() - INTERVAL 7 DAY
  • Keep result sets under 100K rows
  • Use SAMPLE for approximate results:
SELECT avg(price)
FROM trades SAMPLE 0.1
WHERE created_at >= 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)(price) as p95_price FROM trades;
Cardinality estimates 
SELECT uniq(trader_id) as unique_traders FROM trades;
Unnest outcomes array 
SELECT outcome.name
FROM markets
ARRAY JOIN outcomes AS outcome
WHERE platform = 'Polymarket';