Ticket #1129: sqlspeed.py

File sqlspeed.py, 2.6 KB (added by kindly, 3 years ago)

script run do test speed of various ways of doing a "last before this date" query

Line 
1import sys
2from sqlalchemy import create_engine
3from package_ids import package_ids
4from sqlalchemy.sql import text
5
6#engine = create_engine('postgres://david:[email protected]/dgu', echo=True)
7engine = create_engine('postgres://david:[email protected]/dgu')
8conn = engine.connect()
9
10test ='''select package_extra_revision.* from package_extra_revision where package_extra_revision.package_id = '%s' '''
11
12self_join ='''
13select
14package_extra_revision.*
15from
16    package_extra_revision join revision on revision.id = package_extra_revision.revision_id
17join
18    (select
19         pr.id,
20         max(timestamp) ts
21     from
22          package_extra_revision pr
23     join
24          revision r on pr.revision_id = r.id
25     where
26        r.timestamp < current_date - 100 and pr.package_id = '%s'
27     group by pr.id) sub
28 
29on sub.ts = revision.timestamp and sub.id = package_extra_revision.id;
30'''
31
32self_join2 ='''
33select
34package_extra_revision.*
35from
36    package_extra_revision
37join
38    (select
39         pr.id,
40         max(timestamp) ts
41     from
42          package_extra_revision pr
43     where
44        pr.timestamp < current_date - 100 and pr.package_id = '%s'
45     group by pr.id) sub
46 
47on sub.ts = package_extra_revision.timestamp and sub.id = package_extra_revision.id;
48'''
49
50
51distinct ='''
52select
53    distinct on (per.id)
54    per.*
55from
56   package_extra_revision per
57join
58revision r on per.revision_id = r.id where timestamp < current_date - 100 and per.package_id = '%s'
59order by
60   per.id, timestamp desc;
61'''
62
63distinct2 ='''
64select
65    distinct on (per.id)
66    per.*
67from
68   package_extra_revision per
69where
70   timestamp < current_date - 100 and per.package_id = '%s'
71order by
72   per.id, timestamp desc;
73'''
74
75window ='''
76select foo.* from
77    (select
78     per.*,
79     row_number() over (partition by per.id order by timestamp desc) as ord
80     from
81        package_extra_revision per
82     join
83         revision r on r.id = per.revision_id
84     where
85         timestamp < current_date - 100 and  per.package_id = '%s'
86      ) as foo
87where
88     ord = 1;
89'''
90
91window2 ='''
92select foo.* from
93    (select
94     per.*,
95     row_number() over (partition by per.id order by timestamp desc) as ord
96     from
97        package_extra_revision per
98     where
99         timestamp < current_date - 100 and  per.package_id = '%s'
100      ) as foo
101where
102     ord = 1;
103'''
104
105
106if __name__ == '__main__':
107    import datetime
108    statement = locals()[sys.argv[1]]
109
110    start = datetime.datetime.now()
111
112    for num, package in enumerate(package_ids):
113        conn.execute(text(statement % package)).fetchall()
114           
115
116    print datetime.datetime.now() - start
117
118