Python-сообщество

/*create table*/
CREATE TABLE entry (
    id SERIAL,
    data varchar(32),
    tag varchar(32)
);

import random
import hashlib
import psycopg2


CONN_STRING = "dbname=tags user=pmaster"
RECORDS = 1000000
TAGS = 1000
TAGS_FOR_RECORD = 5


def main():
    tags = ['a', 'b', 'c', 'd', 'e']
    progress = 0
    record_list = []
    for i in xrange(RECORDS/TAGS_FOR_RECORD):
        #generating data to be saved                                                                                                                         
	use_tags = random.sample(tags, 5)
	data = hashlib.md5(str(i)).hexdigest()
        record_list.append([data, use_tags])

        #splitting data into portions                                                                                                                        
        if i % (RECORDS/(TAGS_FOR_RECORD*TAGS)) == 0:
	    #printing progress                                                                                                                               
            progress += 1
            print progress

            save_records(record_list)
            record_list = []

            #adding new tags                                                                                                                                 
            tags.append('tag%s' %i)
    save_records(record_list)

    print '---'
    print 'records:', (i+1)*TAGS_FOR_RECORD, 'tags:', len(tags)

def save_records(record_list):
    conn = psycopg2.connect(CONN_STRING)
    cur = conn.cursor()
    for data, tags in record_list:
        for tag in tags:
            cur.execute('insert into entry (data, tag) values (%s, %s)', (data, tag))
    conn.commit()
    cur.close()
    conn.close()

/* get tag cloud of most popular tags*/
select
    count(tag), tag
  from
    entry
  group by
    tag
  order by
    count(tag) desc fetch first 100 rows only
/*  0.39 s */

select
    count(tag), tag
  from
    entry
  where (data in (
	select
		data
	from
		entry
	where
		tag='d'))
		and (tag!= 'd')
  group by
    tag
  order by
	count(tag) desc
    fetch first 100 rows only
/* 0.79 s */

select distinct
     data
  from
    entry
  where
    (tag = 'b') and data in (
	select
		data
	from
		entry
	where
		tag = 'c')
        fetch first 100 rows only
/* 0.25 s */

db.entry.find({tag: {$all: ['c', 'd']}})