Context Navigation

Back to Ticket #3676

Ticket #3676: node_change_index.txt

File node_change_index.txt, 8.6 KB (added by mgood, 20 months ago)
comparison of EXPLAIN with and without key

Line
1	EXPLAIN SELECT path,node_type,change_type,base_path,base_rev
2	FROM node_change WHERE rev=1
3	ORDER BY path;
4
5	SQLite with key
6	===============
7	CREATE TABLE node_change (
8	rev text,
9	path text,
10	node_type text,
11	change_type text,
12	base_path text,
13	base_rev text,
14	UNIQUE (rev,path,change_type)
15	);
16	CREATE INDEX node_change_rev_idx ON node_change (rev);
17
18	addr opcode p1 p2 p3
19	---- -------------- ---------- ---------- ---------------------------------
20	0 Noop 0 0
21	1 Goto 0 30
22	2 Integer 0 0
23	3 OpenRead 0 2
24	4 SetNumColumns 0 6
25	5 Integer 0 0
26	6 OpenRead 2 3 keyinfo(3,BINARY,BINARY)
27	7 Integer 1 0
28	8 NotNull -1 11
29	9 Pop 1 0
30	10 Goto 0 27
31	11 MakeRecord 1 0 aaa
32	12 MemStore 0 0
33	13 MoveGe 2 27
34	14 MemLoad 0 0
35	15 IdxGE 2 27 +
36	16 RowKey 2 0
37	17 IdxIsNull 1 26
38	18 IdxRowid 2 0
39	19 MoveGe 0 0
40	20 Column 0 1
41	21 Column 0 2
42	22 Column 0 3
43	23 Column 0 4
44	24 Column 0 5
45	25 Callback 5 0
46	26 Next 2 14
47	27 Close 0 0
48	28 Close 2 0
49	29 Halt 0 0
50	30 Transaction 0 0
51	31 VerifyCookie 0 2
52	32 Goto 0 2
53	33 Noop 0 0
54
55
56	SQLite without key
57	==================
58	CREATE TABLE node_change (
59	rev text,
60	path text,
61	node_type text,
62	change_type text,
63	base_path text,
64	base_rev text
65	);
66	CREATE INDEX node_change_rev_idx ON node_change (rev);
67
68	addr opcode p1 p2 p3
69	---- -------------- ---------- ---------- ---------------------------------
70	0 OpenVirtual 1 3 keyinfo(1,BINARY)
71	1 Goto 0 49
72	2 Integer 0 0
73	3 OpenRead 0 2
74	4 SetNumColumns 0 6
75	5 Integer 0 0
76	6 OpenRead 2 3 keyinfo(1,BINARY)
77	7 Integer 1 0
78	8 NotNull -1 11
79	9 Pop 1 0
80	10 Goto 0 32
81	11 MakeRecord 1 0 a
82	12 MemStore 0 0
83	13 MoveGe 2 32
84	14 MemLoad 0 0
85	15 IdxGE 2 32 +
86	16 RowKey 2 0
87	17 IdxIsNull 1 31
88	18 IdxRowid 2 0
89	19 MoveGe 0 0
90	20 Column 0 1
91	21 Column 0 2
92	22 Column 0 3
93	23 Column 0 4
94	24 Column 0 5
95	25 MakeRecord 5 0
96	26 Column 0 1
97	27 Sequence 1 0
98	28 Pull 2 0
99	29 MakeRecord 3 0
100	30 IdxInsert 1 0
101	31 Next 2 14
102	32 Close 0 0
103	33 Close 2 0
104	34 OpenPseudo 3 0
105	35 SetNumColumns 3 5
106	36 Sort 1 47
107	37 Integer 1 0
108	38 Column 1 2
109	39 Insert 3 0
110	40 Column 3 0
111	41 Column 3 1
112	42 Column 3 2
113	43 Column 3 3
114	44 Column 3 4
115	45 Callback 5 0
116	46 Next 1 37
117	47 Close 3 0
118	48 Halt 0 0
119	49 Transaction 0 0
120	50 VerifyCookie 0 2
121	51 Goto 0 2
122	52 Noop 0 0
123
124
125	PostgreSQL 8.1 with key
126	=======================
127	CREATE TABLE node_change (
128	rev text,
129	path text,
130	node_type text,
131	change_type text,
132	base_path text,
133	base_rev text,
134	CONSTRAINT node_change_pk PRIMARY KEY (rev,path,change_type)
135	);
136	CREATE INDEX node_change_rev_idx ON node_change (rev);
137
138	Index Scan using node_change_pk on node_change (cost=0.00..8.30 rows=2 width=160)
139	Index Cond: (rev = '1'::text)
140	(2 rows)
141
142
143	PostgreSQL 8.1 without key
144	==========================
145	CREATE TABLE node_change (
146	rev text,
147	path text,
148	node_type text,
149	change_type text,
150	base_path text,
151	base_rev text
152	);
153	CREATE INDEX node_change_rev_idx ON node_change (rev);
154
155	Sort (cost=8.31..8.31 rows=2 width=160)
156	Sort Key: path
157	-> Index Scan using node_change_rev_idx on node_change (cost=0.00..8.30 rows=2 width=160)
158	Index Cond: (rev = '1'::text)
159	(4 rows)

Download in other formats:

Original Format