[GENERAL] SELECT * WHERE id IN (list of ids)
Playing with postgresql I have seen that sometime a very long IN (list
of ids) can rise a max_recursion_error (or something like that).
An easy workaround when the list is computer generated and EXISTS is
infeasible (too slow), is breaking the list in log(n) OR parts.
Like in this python snippet:
if len(candidates) > 2000:
step = int(len(candidates)/math.log(len(candidates)))
parts = []
for i in range(0,len(candidates),step):
candidates_list = ", ".join(map(str, candidates[i:i+step]))
parts.append("%(space)s_id IN (%(candidates_list)s)" % locals())
where_sql = "\nOR\n".join(parts)
This is an example run:
In [1]:a = [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15]
In [2]:for i in range(0,len(a),7):
...:print a[i:i+7]
...:i = i+7
[1, 2, 3, 4, 5, 6, 7]
[8, 9, 10, 11, 12, 13, 14]
[15]
In my (small) experience this trick can speeds-up a lot of queries of
this kind.
Bye,
Matteo Bertini
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] hint unique result fro union
Hello all!I'm quite new to pg, but I'm using it quite a lot in the last few monts.Deeping in new features, I found a question: is it possible to hint an unique result from a select?Mainly, thinking about partitions, I'd like to create a small, frequently accessed partition and a big, rarely accessed partition. I'd like to hint pg to stop the query on the parent partition at the first found item (and so hint not to analyze all the childs), because I know the index I'm using is unique.Possible? Usefull?Thanks, Matteo Bertini
Re: [GENERAL] hint unique result from union
Correct! Didn't noticed the "never executed" in the explain analyze output :-P Steve Atkins ha scritto: > > > "select foo from bar limit 1" ? > > I don't know if there's any guaranteed ordering of results from > a union query, though, and that's what a query on a set of > inherited tables will expand to, pretty much. > > Cheers, > Steve > begin:vcard fn:Matteo Bertini n:Bertini;Matteo email;internet:[EMAIL PROTECTED] tel;cell:+39(0)3284729474 note;quoted-printable:Ci sono 10 tipi di persone, quelle che capiscono il Binario e quelle chen= on lo capiscono.=0D=0A= OpenPGP: http://blog.naufraghi.net/openpgp=0D=0A= ICQ: 33956256 url:http://www.slug.it/naufraghi/ version:2.1 end:vcard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] hint unique result fro union
Yes, the "LIMIT 1" is OK, thanks!I didn't noticed the "not executed" in the explain output!I have done some test comparing a plain table and a partitioned table performance in a particular statistical hypothesis (something explained here too: http://tagschema.com ).Mainly, data is produced and asked following an exponential distribution. So there a few very frequently accessed items and a lot of uncommon items. Partitioning the two sets in different tables, the performance gain is interesting only after a few thousand rows.http://www.slug.it/pgsqlpartSorry, the text is Italian, the code (sql schema and python test code) will be released in a few days (and you will need plpython from CVS if you are using 8.1), nevertheless pictures are already self explaining :-P... I have seen a similar benchmark online but I cannot find it now to compare.Sorry for my English, and open to comment about my simple benchmark, Matteo Bertini2006/8/16, Steve Atkins <[EMAIL PROTECTED]>: On Aug 16, 2006, at 8:23 AM, Matteo Bertini wrote:> Hello all!>> I'm quite new to pg, but I'm using it quite a lot in the last few> monts.>> Deeping in new features, I found a question: is it possible to hint > an unique result from a select?>> Mainly, thinking about partitions, I'd like to create a small,> frequently accessed partition and a big, rarely accessed partition.>> I'd like to hint pg to stop the query on the parent partition at > the first found item (and so hint not to analyze all the childs),> because I know the index I'm using is unique.>> Possible? Usefull?>"select foo from bar limit 1" ? I don't know if there's any guaranteed ordering of results froma union query, though, and that's what a query on a set ofinherited tables will expand to, pretty much.Cheers, Steve ---(end of broadcast)---TIP 5: don't forget to increase your free space map settings
