Skip to content

Commit ef0abba

Browse files
anayratjeanas
anayrat
and
jeanas
authored
Add PostgreSQL Explain lexer (#2398)
This lexer add support for PostgreSQL Explain plan : https://www.postgresql.org/docs/current/sql-explain.html This was heavily inspired by Maxence Ahlouche work, thanks to him : https://github.com/maahl/pg_explain_lexer Co-authored-by: Jean Abou Samra <jean@abou-samra.fr>
1 parent 3c6e2af commit ef0abba

6 files changed

Lines changed: 3125 additions & 4 deletions

File tree

‎AUTHORS‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,7 @@ Other contributors, listed alphabetically, are:
1111
* Ali Afshar -- image formatter
1212
* Thomas Aglassinger -- Easytrieve, JCL, Rexx, Transact-SQL and VBScript
1313
lexers
14+
* Maxence Ahlouche -- PostgreSQL Explain lexer
1415
* Muthiah Annamalai -- Ezhil lexer
1516
* Kumar Appaiah -- Debian control lexer
1617
* Andreas Amann -- AppleScript lexer
@@ -162,6 +163,7 @@ Other contributors, listed alphabetically, are:
162163
* Paulo Moura -- Logtalk lexer
163164
* Mher Movsisyan -- DTD lexer
164165
* Dejan Muhamedagic -- Crmsh lexer
166+
* Adrien Nayrat -- PostgreSQL Explain lexer
165167
* Ana Nelson -- Ragel, ANTLR, R console lexers
166168
* David Neto, Google LLC -- WebGPU Shading Language lexer
167169
* Kurt Neufeld -- Markdown lexer
@@ -191,7 +193,7 @@ Other contributors, listed alphabetically, are:
191193
* Justin Reidy -- MXML lexer
192194
* Norman Richards -- JSON lexer
193195
* Corey Richardson -- Rust lexer updates
194-
* Fabrizio Riguzzi -- cplint leder
196+
* Fabrizio Riguzzi -- cplint leder
195197
* Lubomir Rintel -- GoodData MAQL and CL lexers
196198
* Andre Roberge -- Tango style
197199
* Georg Rollinger -- HSAIL lexer

‎pygments/lexers/_mapping.py‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -370,6 +370,7 @@
370370
'PortugolLexer': ('pygments.lexers.pascal', 'Portugol', ('portugol',), ('*.alg', '*.portugol'), ()),
371371
'PostScriptLexer': ('pygments.lexers.graphics', 'PostScript', ('postscript', 'postscr'), ('*.ps', '*.eps'), ('application/postscript',)),
372372
'PostgresConsoleLexer': ('pygments.lexers.sql', 'PostgreSQL console (psql)', ('psql', 'postgresql-console', 'postgres-console'), (), ('text/x-postgresql-psql',)),
373+
'PostgresExplainLexer': ('pygments.lexers.sql', 'PostgreSQL EXPLAIN dialect', ('postgres-explain',), ('*.explain',), ('text/x-postgresql-explain',)),
373374
'PostgresLexer': ('pygments.lexers.sql', 'PostgreSQL SQL dialect', ('postgresql', 'postgres'), (), ('text/x-postgresql',)),
374375
'PovrayLexer': ('pygments.lexers.graphics', 'POVRay', ('pov',), ('*.pov', '*.inc'), ('text/x-povray',)),
375376
'PowerShellLexer': ('pygments.lexers.shell', 'PowerShell', ('powershell', 'pwsh', 'posh', 'ps1', 'psm1'), ('*.ps1', '*.psm1'), ('text/x-powershell',)),

‎pygments/lexers/_postgres_builtins.py‎

Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -571,6 +571,61 @@
571571
'RETURN', 'REVERSE', 'SQLSTATE', 'WHILE',
572572
)
573573

574+
# Most of these keywords are from ExplainNode function
575+
# in src/backend/commands/explain.c
576+
577+
EXPLAIN_KEYWORDS = (
578+
'Aggregate',
579+
'Append',
580+
'Bitmap Heap Scan',
581+
'Bitmap Index Scan',
582+
'BitmapAnd',
583+
'BitmapOr',
584+
'CTE Scan',
585+
'Custom Scan',
586+
'Delete',
587+
'Foreign Scan',
588+
'Function Scan',
589+
'Gather Merge',
590+
'Gather',
591+
'Group',
592+
'GroupAggregate',
593+
'Hash Join',
594+
'Hash',
595+
'HashAggregate',
596+
'Incremental Sort',
597+
'Index Only Scan',
598+
'Index Scan',
599+
'Insert',
600+
'Limit',
601+
'LockRows',
602+
'Materialize',
603+
'Memoize',
604+
'Merge Append',
605+
'Merge Join',
606+
'Merge',
607+
'MixedAggregate',
608+
'Named Tuplestore Scan',
609+
'Nested Loop',
610+
'ProjectSet',
611+
'Recursive Union',
612+
'Result',
613+
'Sample Scan',
614+
'Seq Scan',
615+
'SetOp',
616+
'Sort',
617+
'SubPlan',
618+
'Subquery Scan',
619+
'Table Function Scan',
620+
'Tid Range Scan',
621+
'Tid Scan',
622+
'Unique',
623+
'Update',
624+
'Values Scan',
625+
'WindowAgg',
626+
'WorkTable Scan',
627+
)
628+
574629

575630
if __name__ == '__main__': # pragma: no cover
576631
import re

‎pygments/lexers/sql.py‎

Lines changed: 191 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,9 @@
3030
- highlights errors in the output and notification levels;
3131
- handles psql backslash commands.
3232
33+
`PostgresExplainLexer`
34+
A lexer to highlight Postgres execution plan.
35+
3336
The ``tests/examplefiles`` contains a few test files with data to be
3437
parsed by these lexers.
3538
@@ -45,7 +48,7 @@
4548
from pygments.lexers import get_lexer_by_name, ClassNotFound
4649

4750
from pygments.lexers._postgres_builtins import KEYWORDS, DATATYPES, \
48-
PSEUDO_TYPES, PLPGSQL_KEYWORDS
51+
PSEUDO_TYPES, PLPGSQL_KEYWORDS, EXPLAIN_KEYWORDS
4952
from pygments.lexers._mysql_builtins import \
5053
MYSQL_CONSTANTS, \
5154
MYSQL_DATATYPES, \
@@ -57,8 +60,8 @@
5760

5861

5962
__all__ = ['PostgresLexer', 'PlPgsqlLexer', 'PostgresConsoleLexer',
60-
'SqlLexer', 'TransactSqlLexer', 'MySqlLexer',
61-
'SqliteConsoleLexer', 'RqlLexer']
63+
'PostgresExplainLexer', 'SqlLexer', 'TransactSqlLexer',
64+
'MySqlLexer', 'SqliteConsoleLexer', 'RqlLexer']
6265

6366
line_re = re.compile('.*?\n')
6467
sqlite_prompt_re = re.compile(r'^(?:sqlite| ...)>(?= )')
@@ -368,6 +371,191 @@ def get_tokens_unprocessed(self, data):
368371
return
369372

370373

374+
class PostgresExplainLexer(RegexLexer):
375+
"""
376+
Handle PostgreSQL EXPLAIN output
377+
378+
"""
379+
380+
name = 'PostgreSQL EXPLAIN dialect'
381+
aliases = ['postgres-explain']
382+
filenames = ['*.explain']
383+
mimetypes = ['text/x-postgresql-explain']
384+
385+
tokens = {
386+
'root': [
387+
(r'(:|\(|\)|ms|kB|->|\.\.|\,)', Punctuation),
388+
(r'(\s+)', Whitespace),
389+
390+
# This match estimated cost and effectively measured counters with ANALYZE
391+
# Then, we move to instrumentation state
392+
(r'(cost)(=?)', bygroups(Name.Class, Punctuation), 'instrumentation'),
393+
(r'(actual)( )(=?)', bygroups(Name.Class, Whitespace, Punctuation), 'instrumentation'),
394+
395+
# Misc keywords
396+
(words(('actual', 'Memory Usage', 'Memory', 'Buckets', 'Batches',
397+
'originally', 'row', 'rows', 'Hits', 'Misses',
398+
'Evictions', 'Overflows'), suffix=r'\b'),
399+
Comment.Single),
400+
401+
(r'(hit|read|dirtied|written|write|time|calls)(=)', bygroups(Comment.Single, Operator)),
402+
(r'(shared|temp|local)', Keyword.Pseudo),
403+
404+
# We move to sort state in order to emphasize specific keywords (especially disk access)
405+
(r'(Sort Method)(: )', bygroups(Comment.Preproc, Punctuation), 'sort'),
406+
407+
# These keywords can be followed by an object, like a table
408+
(r'(Sort Key|Group Key|Presorted Key|Hash Key)(:)( )',
409+
bygroups(Comment.Preproc, Punctuation, Whitespace), 'object_name'),
410+
(r'(Cache Key|Cache Mode)(:)( )', bygroups(Comment, Punctuation, Whitespace), 'object_name'),
411+
412+
# These keywords can be followed by a predicate
413+
(words(('Join Filter', 'Subplans Removed', 'Filter', 'Merge Cond',
414+
'Hash Cond', 'Index Cond', 'Recheck Cond', 'Heap Blocks',
415+
'TID Cond', 'Run Condition', 'Order By', 'Function Call',
416+
'Table Function Call', 'Inner Unique', 'Params Evaluated',
417+
'Single Copy', 'Sampling', 'One-Time Filter', 'Output',
418+
'Relations', 'Remote SQL'), suffix=r'\b'),
419+
Comment.Preproc, 'predicate'),
420+
421+
# Special keyword to handle ON CONFLICT
422+
(r'Conflict ', Comment.Preproc, 'conflict'),
423+
424+
# Special keyword for InitPlan or SubPlan
425+
(r'(InitPlan|SubPlan)( )(\d+)( )',
426+
bygroups(Keyword, Whitespace, Number.Integer, Whitespace),
427+
'init_plan'),
428+
429+
(words(('Sort Method', 'Join Filter', 'Planning time',
430+
'Planning Time', 'Execution time', 'Execution Time',
431+
'Workers Planned', 'Workers Launched', 'Buffers',
432+
'Planning', 'Worker', 'Query Identifier', 'Time',
433+
'Full-sort Groups'), suffix=r'\b'), Comment.Preproc),
434+
435+
# Emphasize these keywords
436+
437+
(words(('Rows Removed by Join Filter', 'Rows Removed by Filter',
438+
'Rows Removed by Index Recheck',
439+
'Heap Fetches', 'never executed'),
440+
suffix=r'\b'), Name.Exception),
441+
(r'(I/O Timings)(:)( )', bygroups(Name.Exception, Punctuation, Whitespace)),
442+
443+
(words(EXPLAIN_KEYWORDS, suffix=r'\b'), Keyword),
444+
445+
# join keywords
446+
(r'((Right|Left|Full|Semi|Anti) Join)', Keyword.Type),
447+
(r'(Parallel |Async |Finalize |Partial )', Comment.Preproc),
448+
(r'Backward', Comment.Preproc),
449+
(r'(Intersect|Except|Hash)', Comment.Preproc),
450+
451+
(r'(CTE)( )(\w*)?', bygroups(Comment, Whitespace, Name.Variable)),
452+
453+
454+
# Treat "on" and "using" as a punctuation
455+
(r'(on|using)', Punctuation, 'object_name'),
456+
457+
458+
# strings
459+
(r"'(''|[^'])*'", String.Single),
460+
# numbers
461+
(r'\d+\.\d+', Number.Float),
462+
(r'(\d+)', Number.Integer),
463+
464+
# boolean
465+
(r'(true|false)', Name.Constant),
466+
# explain header
467+
(r'\s*QUERY PLAN\s*\n\s*-+', Comment.Single),
468+
# Settings
469+
(r'(Settings)(:)( )', bygroups(Comment.Preproc, Punctuation, Whitespace), 'setting'),
470+
471+
# Handle JIT counters
472+
(r'(JIT|Functions|Options|Timing)(:)', bygroups(Comment.Preproc, Punctuation)),
473+
(r'(Inlining|Optimization|Expressions|Deforming|Generation|Emission|Total)', Keyword.Pseudo),
474+
475+
# Handle Triggers counters
476+
(r'(Trigger)( )(\S*)(:)( )',
477+
bygroups(Comment.Preproc, Whitespace, Name.Variable, Punctuation, Whitespace)),
478+
479+
],
480+
'expression': [
481+
# matches any kind of parenthesized expression
482+
# the first opening paren is matched by the 'caller'
483+
(r'\(', Punctuation, '#push'),
484+
(r'\)', Punctuation, '#pop'),
485+
(r'(never executed)', Name.Exception),
486+
(r'[^)(]+', Comment),
487+
],
488+
'object_name': [
489+
490+
# This is a cost or analyze measure
491+
(r'(\(cost)(=?)', bygroups(Name.Class, Punctuation), 'instrumentation'),
492+
(r'(\(actual)( )(=?)', bygroups(Name.Class, Whitespace, Punctuation), 'instrumentation'),
493+
494+
# if object_name is parenthesized, mark opening paren as
495+
# punctuation, call 'expression', and exit state
496+
(r'\(', Punctuation, 'expression'),
497+
(r'(on)', Punctuation),
498+
# matches possibly schema-qualified table and column names
499+
(r'\w+(\.\w+)*( USING \S+| \w+ USING \S+)', Name.Variable),
500+
(r'\"?\w+\"?(?:\.\"?\w+\"?)?', Name.Variable),
501+
(r'\'\S*\'', Name.Variable),
502+
503+
# if we encounter a comma, another object is listed
504+
(r',\n', Punctuation, 'object_name'),
505+
(r',', Punctuation, 'object_name'),
506+
507+
# special case: "*SELECT*"
508+
(r'"\*SELECT\*( \d+)?"(.\w+)?', Name.Variable),
509+
(r'"\*VALUES\*(_\d+)?"(.\w+)?', Name.Variable),
510+
(r'"ANY_subquery"', Name.Variable),
511+
512+
# Variable $1 ...
513+
(r'\$\d+', Name.Variable),
514+
# cast
515+
(r'::\w+', Name.Variable),
516+
(r' +', Whitespace),
517+
(r'"', Punctuation),
518+
(r'\[\.\.\.\]', Punctuation),
519+
(r'\)', Punctuation, '#pop'),
520+
],
521+
'predicate': [
522+
# if predicate is parenthesized, mark paren as punctuation
523+
(r'(\()([^\n]*)(\))', bygroups(Punctuation, Name.Variable, Punctuation), '#pop'),
524+
# otherwise color until newline
525+
(r'[^\n]*', Name.Variable, '#pop'),
526+
],
527+
'instrumentation': [
528+
(r'=|\.\.', Punctuation),
529+
(r' +', Whitespace),
530+
(r'(rows|width|time|loops)', Name.Class),
531+
(r'\d+\.\d+', Number.Float),
532+
(r'(\d+)', Number.Integer),
533+
(r'\)', Punctuation, '#pop'),
534+
],
535+
'conflict': [
536+
(r'(Resolution: )(\w+)', bygroups(Comment.Preproc, Name.Variable)),
537+
(r'(Arbiter \w+:)', Comment.Preproc, 'object_name'),
538+
(r'(Filter: )', Comment.Preproc, 'predicate'),
539+
],
540+
'setting': [
541+
(r'([a-z_]*?)(\s*)(=)(\s*)(\'.*?\')', bygroups(Name.Attribute, Whitespace, Operator, Whitespace, String)),
542+
(r'\, ', Punctuation),
543+
],
544+
'init_plan': [
545+
(r'\(', Punctuation),
546+
(r'returns \$\d+(,\$\d+)?', Name.Variable),
547+
(r'\)', Punctuation, '#pop'),
548+
],
549+
'sort': [
550+
(r':|kB', Punctuation),
551+
(r'(quicksort|top-N|heapsort|Average|Memory|Peak)', Comment.Prepoc),
552+
(r'(external|merge|Disk|sort)', Name.Exception),
553+
(r'(\d+)', Number.Integer),
554+
(r' +', Whitespace),
555+
],
556+
}
557+
558+
371559
class SqlLexer(RegexLexer):
372560
"""
373561
Lexer for Structured Query Language. Currently, this lexer does

0 commit comments

Comments
 (0)