RonyForAI/Mirage_DB_RL
0
1o
2 A_�i ` � @ sd U d Z ddlmZ ddlZddlZddlmZmZ ddlm Z m3Z4 eG dd� d��Z eG dd � d ��Z eG d5d � d ��Z
e d d
de dddddd�e dddddd�e dddddd�gd �e d d d e d d!d"ddd�e d#d$d%ddd�e d&d'd(ddd�gd �e d)d
d*e dddddd�e d+d,d-ddd�e d.d/ddd0d�gd �gZd1ed2< e d3d
d4e d5d6d"dd%d�e d7d!ddd(d�e ddddd"d�e ddd8ddd�e d+d,d-ddd�gd �e d9d:d;e d<d=ddd>d�e d d?d%ddd�e d@dAd>ddBd�e dCd!d"ddDd�e dEdFdGddHd�gd �e dIdJdKe dLdMdddNd�e d#d6d(dd(d�e dOddDdd"d�e dPd6d"ddNd�e dQdRdSddHd�gd �gZd1edT< e dUd
dVe ddWd(ddd�e d+dXd(dd"d�e dd6ddd(d�e ddYd(ddd�e d7d6ddd%d�e d5dZd"ddNd�e d[d\ddd]d�gd �e d^dJd_e d`dWd(dd(d�e dadXd(dd8d�e dOd?d(dd"d�e dbdcddd>d�e dPddd(dded�e d#dZd"ddd�e dLd=ddd>d�gd �e dfd:dge d@dWd(dd"d�e dhdXd(dded�e d d?d(ddd�e dCdcdddNd�e d<did"dd>d�e djdkdddld�e dmd\ddd-d�gd �gZd1edn< e
dodpdodqedr�e
dsdtdsduedr�e
dvdwdvdxedr�dy�Zdzed{< d�d�d��Zd�d�d��Zd�d�d�d��Zd�d�d��Z d�d�d�d��ZdS )�u 6tasks.py — Enterprise-grade query optimization task definitions for Mirage_RL.7 8Each task tier (easy / medium / hard) contains a pool of production-realistic9query scenarios sampled randomly per episode so agents cannot memorize solutions.10 11Domains covered:12 - E-commerce (OLTP): orders, customers, products, inventory, suppliers13 - Analytics (OLAP): events, sessions, campaigns, conversions, attribution14 - Financial (OLAP): transactions, accounts, merchants, fraud signals, risk15 16Key design decisions:17 - Table cardinalities match realistic production scales (10K – 1B rows)18 - Cardinality estimation noise (log-normal) simulates the core real-world19 challenge: a planner's row estimates are always wrong. Noise sigma maps to:20 σ=0.04 excellent statistics (small, frequently vacuumed)21 σ=0.15 typical OLTP tables22 σ=0.25 typical analytics/OLAP tables23 σ=0.40 poor statistics (large tables, column correlations)24 σ=0.60 very poor (event tables, multi-column predicates, data skew)25� )� annotationsN)� dataclass�field)�List�Optionalc @ s: e Zd ZU dZded< ded< ded< ded< ded < d26S ) � TableSpecz0Configuration for one table in a query scenario.�str�name�int� true_rows�float� selectivity� has_index� noise_sigmaN��__name__�27__module__� __qualname__�__doc__�__annotations__� r r �:D:\Meta Hackathon DBMS Optimizer\Mirage_RL\server\tasks.pyr # s 28 r c @ s2 e Zd ZU dZded< ded< ded< ded< dS ) �Scenarioz&A complete multi-table query scenario.r r �domain�
query_context�List[TableSpec]�tablesNr r r r r r - s 29 r c @ s6 e Zd ZU ded< ded< ded< ded< ded< dS ) �30TaskConfigr �task_idr �31difficulty� description�List[Scenario]� scenariosN)r r r r r r r r r 6 s 32 r Zecommerce_catalog_lookupZ ecommercez�SELECT p.name, c.label, s.region FROM products JOIN categories ON p.category_id=c.id JOIN suppliers ON p.supplier_id=s.id WHERE c.segment='Electronics' AND s.active=trueZproductsi � g{�G�z�?� g�������?)r r
r r �33categoriesi' g�������?g� ��Q��?Z suppliersiP� g� ��Q��?)r r r r Zsaas_active_subscriptionsZsaasz�SELECT u.email, a.plan, s.renewal_date FROM users JOIN accounts ON u.account_id=a.id JOIN subscriptions ON a.id=s.account_id WHERE a.tier='enterprise' AND s.status='active'�usersi�� g{�G�z�?Zaccountsi 5 g333333�?Z
subscriptionsi�O g�������?Zinventory_reorder_checkz�SELECT p.sku, w.location, i.quantity FROM products JOIN warehouses ON i.warehouse_id=w.id JOIN inventory ON p.id=i.product_id WHERE i.quantity < p.reorder_point AND w.region='US-WEST'Z34warehousesi� g�������?Z inventoryi�%& g� ��Q��?r! �EASY_SCENARIOSZecommerce_order_fulfillmenta SELECT o.id, c.name, p.sku, cat.label, w.region FROM orders JOIN customers ON o.customer_id=c.id JOIN products ON o.product_id=p.id JOIN categories ON p.category_id=cat.id JOIN warehouses ON o.warehouse_id=w.id WHERE o.status='pending' AND o.created_at > NOW() - INTERVAL '7 days'Zordersi��� Z customers皙�����?Zmarketing_funnel_analytics� analyticsz�SELECT s.id, u.segment, c.name, cv.revenue, ch.source FROM sessions JOIN users ON s.user_id=u.id JOIN campaigns ON s.campaign_id=c.id JOIN conversions ON s.id=cv.session_id JOIN channels ON c.channel_id=ch.id WHERE c.type='paid' AND cv.revenue > 0�sessionsi���g �?i@KL Z campaignsi�� g�Q� ��?Z conversionsg35ףp=36�?�channelsi� gffffff�?g{�G�z�?Z financial_transaction_summaryZ financiala" SELECT t.amount, a.holder, m.name, rs.score, cur.symbol FROM transactions JOIN accounts ON t.account_id=a.id JOIN merchants ON t.merchant_id=m.id JOIN risk_scores ON a.id=rs.account_id JOIN currencies ON t.currency_code=cur.code WHERE t.created_at >= CURRENT_DATE - 30 AND a.status='active'Z transactionsi ��g�������?Z merchantsZ risk_scoresZ37currencies�� 皙�����?�MEDIUM_SCENARIOSZ ecommerce_full_pipeline_audita� SELECT o.id, oi.qty, p.sku, cat.label, c.name, s.contact, w.region FROM orders JOIN order_items ON o.id=oi.order_id JOIN products ON oi.product_id=p.id JOIN categories ON p.category_id=cat.id JOIN customers ON o.customer_id=c.id JOIN suppliers ON p.supplier_id=s.id JOIN warehouses ON oi.warehouse_id=w.id WHERE o.created_at >= '2024-01-01' AND c.country='US' AND cat.segment='Electronics'i@B i��- i z i -1Z order_itemsi Zbgffffff�?Zfraud_detection_pipelineat SELECT t.id, a.holder, m.category, fl.label, rs.score, d.fingerprint, loc.country FROM transactions JOIN accounts ON t.account_id=a.id JOIN merchants ON t.merchant_id=m.id JOIN fraud_labels ON t.id=fl.transaction_id JOIN risk_scores ON a.id=rs.account_id JOIN devices ON t.device_id=d.id JOIN locations ON t.location_id=loc.id WHERE t.amount > 10000 AND fl.is_flagged=true� locationsZ fraud_labelsZdevicesi $� i � g333333�?Zuser_journey_attributionan SELECT e.event_type, s.duration, u.segment, c.name, cv.revenue, ab.variant, pv.url FROM events JOIN sessions ON e.session_id=s.id JOIN users ON s.user_id=u.id JOIN campaigns ON s.campaign_id=c.id JOIN conversions ON s.id=cv.session_id JOIN ab_tests ON u.id=ab.user_id JOIN page_views ON s.id=pv.session_id WHERE s.started_at >= CURRENT_DATE - 7 AND c.status='active'Zab_testsi��� �eventsi H�� �?Z38page_views�HARD_SCENARIOS�easyu' OLTP Join Optimizer — 3-Table Queriesz�Production OLTP queries joining 3 tables from e-commerce and SaaS schemas. All indexes are available. Statistics are accurate. Goal: select the optimal join order and strategy. Scoring penalises nested-loop joins and missed indexes.)r r r r r" �mediumu= OLAP Join Optimizer — 5-Table Queries with Estimation Noiseu� Analytical queries joining 5 tables across e-commerce, marketing, and financial schemas. Some tables lack indexes. Cardinality estimates contain realistic noise (σ 0.05–0.25), requiring the agent to reason under uncertainty about true table sizes.�harduJ Complex OLAP Join Optimizer — 7-Table Queries with High Estimation Noiseu5 Enterprise analytical queries joining 7 tables with billion-row event tables, missing indexes, and high cardinality estimation noise (σ up to 0.60). Models real-world conditions: data skew, stale statistics, and column correlations that cause traditional planners to underestimate result sizes by 10–100×.)r2 r3 r4 zdict[str, TaskConfig]�TASKSr r39 r r �rng�
random.Random�returnc C s4 |dkr| S |� d|�}t| t�|� �}td|�S )u� 40 Simulate cardinality estimation error using log-normal noise.41 42 real planners: estimated_rows = true_rows × exp(N(0, σ²))43 44 σ=0.05 → ~5% error (excellent statistics)45 σ=0.15 → ~15% error (typical OLTP)46 σ=0.25 → ~28% error (typical OLAP)47 σ=0.40 → ~49% error (stale statistics / large tables)48 σ=0.60 → ~82% error (data skew, column correlations)49 r � r# )�gaussr50 �math�exp�max)r r r6 Z51log_factorZ estimatedr r r �apply_estimation_noise, s52 53r> r r �tuple[float, float]c C sZ dd� | D �}t |dd� d�}t |dd� dd�}dd
d�}||dd�}||dd�}||fS )u^ 54 Analytical worst/best cost bounds for a scenario, accounting for cascading sizes.55 Joining early inflates all subsequent step costs via an additive intermediate size penalty.56 57 Worst: worst ordering by size (largest first), nested-loop (2.0×), no index58 Best: best ordering by size (smallest first), merge-sort (0.8×), use index59 c S s g | ]60}|j |j |f�qS r �r r
��.0�tr r r �61<listcomp>L s z'compute_cost_bounds.<locals>.<listcomp>c S � | d S �Nr r ��xr r r �<lambda>M � z%compute_cost_bounds.<locals>.<lambda>)�keyc S rE rF r rG r r r rI N rJ T)rK �reverse�is_worst�boolr8 r c S sz d}d}| D ]4\}}|r|j |j d | }n|jr |j d n|j }||j d | }||7 }|dkr6|}q||9 }q|S )Nr9 � @r0 r, )r r
r )Z62order_listrM �63total_cost� running_sizeZ output_sizerC Z step_cost� best_baser r r �
simulate_costP s 64z*compute_cost_bounds.<locals>.simulate_cost)rM FN)rM rN r8 r )�sorted)r �outputsZ65best_orderZ worst_orderrS �worst�bestr r r �compute_cost_boundsC s 66 rX � �?�tablerQ c C sR t d|d �}| j| j d | }| jr| jd n| j}|| j d | }||fS )zGWorst/best cost bounds for a single table step, given additive penalty.r9 rY rO r0 r, )r= r r
r )rZ rQ �penaltyrV rR rW r r r �compute_step_cost_boundsj s67 r\ � chosen_order� List[int]c s� t |�� � dkr68dS dd� |D ���fdd�| D �}t���tt���}d� �fd69d � }||�}||�}|d k r:dS d|| }ttd
td|���S )u 70 Score the join ordering quality using log-normalised rank mismatch.71 72 Optimal order: join tables with smallest (true_rows × selectivity) first,73 because this minimises intermediate result sizes throughout the sequence.74 Joining a large-output table early compounds the cost of every subsequent75 join — this is the core of real query optimisation difficulty.76 77 Method:78 1. Compute each table's output size = true_rows × selectivity79 2. For each position k, compare the log-output of the chosen k-th table80 against the log-output of the k-th optimal table (sorted ascending)81 3. Normalise mismatch against the worst possible ordering (descending)82 83 Returns 1.0 for a perfectly sorted order, 0.0 for reverse optimal.84 r# rY c S s g | ]}|j |j �qS r r@ rA r r r rD � s z)compute_order_quality.<locals>.<listcomp>c s g | ]}� | �qS r r )rB �i)rU r r rD � s �
order_outputs� List[float]r8 r c s t �� fdd�t��D ��S )Nc 3 s0 � | ]}t t��| �t�� | � �V qd S �N)�absr; �log1p)rB �k)�optimal_outputsr` r r � <genexpr>� s85 � �86�z>compute_order_quality.<locals>.log_mismatch.<locals>.<genexpr>)�sum�range�r` )�nrf rj r � log_mismatch� s �z+compute_order_quality.<locals>.log_mismatchg��&� .>r9 N)r` ra r8 r )�lenrT �list�reversedr r= �min)r] r Zchosen_outputsZ
worst_outputsrl Zactual_mismatchZ max_mismatch�scorer )rk rf rU r �compute_order_qualityy s rr �87final_cost�Optional[List[int]]c C s� t | �\}}||kr
d}nttdtd|| || ���}|dur/t|�t| �kr/t|| �}n|}d| d| }ttdtd|���S )u+ 88 Grade a completed episode. Combined score = 60% method + 40% order.89 90 method_score: how well the agent chose join algorithms and index usage.91 = (worst_cost - actual_cost) / (worst_cost - best_cost)92 worst = all nested-loop, no indexes93 best = all merge-sort, use index when available94 95 order_score: how well the agent sequenced the joins.96 Optimal = smallest-output tables first (minimises intermediate blowup).97 Computed via log-normalised rank mismatch vs optimal ordering.98 Only applied when all tables have been joined (full episode).99 100 Combined = 0.60 × method_score + 0.40 × order_score101 102 This ensures:103 - A model that always picks merge_sort but joins biggest tables first104 scores ~0.60 instead of ~1.00 on hard tasks.105 - A model with correct ordering but mediocre method choices still106 receives partial credit from the order component.107 - Difficulty genuinely increases: easy (3 tables, 6 orderings) vs108 hard (7 tables, 5040 orderings) differentiates planning depth.109 rY r9 Ng333333�?r' )rX r r= rp rm rr )r rs r] rV rW Z method_scoreZ order_score�combinedr r r �grade� s rv )r r110 r r r6 r7 r8 r111 )r r r8 r? )rY )rZ r rQ r r8 r? )r] r^ r r r8 r rb )r r rs r r] rt r8 r )r �112__future__r r; �random� dataclassesr r �typingr r r r r r& r r- r1 r5 r> rX r\ rr rv r r r r �<module> s ������ �2������ �> ��113�� �� �O� � ��114,115 '1161�