> ## 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.

# Tips & Best Practices

> Performance optimization for Probalytics queries

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

```sql theme={null}
-- 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%';
```

```sql theme={null}
-- 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.

```sql theme={null}
-- 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.

```sql theme={null}
-- 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

```sql theme={null}
-- 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:

```sql theme={null}
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**

```sql theme={null}
SELECT quantile(0.95)(normalized_price) as p95_price FROM fills;
```

**Cardinality estimates**

```sql theme={null}
SELECT uniq(taker_id) as unique_takers FROM fills;
```

**Unnest outcomes array**

```sql theme={null}
SELECT outcome.name
FROM markets
ARRAY JOIN outcomes AS outcome
WHERE platform = 'POLYMARKET';
```

**Best bid/ask from orderbook**

```sql theme={null}
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;
```