Re: [PERFORM] Reverse Key Index
Sven R. Kunze srku...@tbz-pariv.de writes: does PostgreSQL support the concept of reverse key indexing as described here? I couldn't find any documentation on this yet. http://www.toadworld.com/platforms/oracle/w/wiki/11075.reverse-key-index-from-the-concept-to-internals.aspx There's nothing built-in for that (and frankly, it doesn't sound useful enough that we'd ever add it). You could get the effect easily enough with an expression index on a byte-reversing function. A related thing that people often do is create an index on a hash function. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Reverse Key Index
Hi, does PostgreSQL support the concept of reverse key indexing as described here? I couldn't find any documentation on this yet. http://www.toadworld.com/platforms/oracle/w/wiki/11075.reverse-key-index-from-the-concept-to-internals.aspx Regards, -- Sven R. Kunze TBZ-PARIV GmbH, Bernsdorfer Str. 210-212, 09130 Chemnitz Tel: +49 (0)371 33714721, Fax: +49 (0)371 5347920 e-mail: srku...@tbz-pariv.de web: www.tbz-pariv.de Geschäftsführer: Dr. Reiner Wohlgemuth Sitz der Gesellschaft: Chemnitz Registergericht: Chemnitz HRB 8543 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Reverse Key Index
Thanks for the immediate reply. I understand the use case is quite limited. On the other hand, I see potential when it comes to applications which use PostgreSQL. There, programmers would have to change a lot of code to tweak existing (and more importantly working) queries to hash/reverse an id column first. Using ORMs would make this change even more painful and maybe even impossible. When reading https://richardfoote.wordpress.com/2008/01/14/introduction-to-reverse-key-indexes-part-i/ carefully, it also seems to work with index scan partially in case of equality comparisons. On 14.02.2015 19:18, Tom Lane wrote: Sven R. Kunze srku...@tbz-pariv.de writes: does PostgreSQL support the concept of reverse key indexing as described here? I couldn't find any documentation on this yet. http://www.toadworld.com/platforms/oracle/w/wiki/11075.reverse-key-index-from-the-concept-to-internals.aspx There's nothing built-in for that (and frankly, it doesn't sound useful enough that we'd ever add it). You could get the effect easily enough with an expression index on a byte-reversing function. A related thing that people often do is create an index on a hash function. regards, tom lane -- Sven R. Kunze TBZ-PARIV GmbH, Bernsdorfer Str. 210-212, 09130 Chemnitz Tel: +49 (0)371 33714721, Fax: +49 (0)371 5347920 e-mail: srku...@tbz-pariv.de web: www.tbz-pariv.de Geschäftsführer: Dr. Reiner Wohlgemuth Sitz der Gesellschaft: Chemnitz Registergericht: Chemnitz HRB 8543 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Configuration tips for very large database
On 02/13/2015 12:19 AM, Claudio Freire wrote: I have a table with ~800M rows, wide ones, that runs reporting queries quite efficiently (usually seconds). Of course, the queries don't traverse the whole table. That wouldn't be efficient. That's probably the key there, don't make you database process the whole thing every time if you expect it to be scalable. What kind of queries are you running that have slowed down? Post an explain analyze so people can diagnose. Possibly it's a query/indexing issue rather than a hardware one. Thanks everybody for the answers. At the moment I don't have the queries at hand (saturday:-) ). I'll post them next week. I'd really like to avoid data partitioning if possible. It's a thing that gives me a strong stomach ache. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
