From: Markus Schaber <schabi@logix-tt.com>
To: Aaron Turner <synfinatic@gmail.com>
Cc: Jim C. Nasby <jnasby@pervasive.com>
Cc: Matthew T. O'Connor <matthew@zeut.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: 10+hrs vs 15min because of just one index
Date: Sun, 12 Feb 2006 22:04:18 +0100
Message-ID: <43EFA2D2.1050609@logix-tt.com> (raw)
In-Reply-To: <1ca1c1410602112358h1e0e9696lff8f172769dc587e@mail.gmail.com>
References: <1ca1c1410602100016p2b5fdcc4wbf45612d7efc5fdf@mail.gmail.com>
<43ECC9BF.2030107@zeut.net>
<1ca1c1410602100924s191e7cddo4bda80d8f8d7cb6d@mail.gmail.com>
<20060211212453.GT57845@pervasive.com>
<1ca1c1410602112358h1e0e9696lff8f172769dc587e@mail.gmail.com>
Hi, Aaron,
Aaron Turner wrote:
> 4) Does decoding the data (currently base64) and storing the binary
> data improve the distribution of the index, thereby masking it more
> efficent?
Yes, but then you should not use varchar, but a bytea.
If your data is some numer internally, numeric or decimal may be even
better.
If most of your data is different in the first 8 bytes, it may also make
sense to duplicate them into a bigint, and create the bigint on them.
Then you can use AND in your query to test for the 8 bytes (uses index)
and the bytea. Ugly, but may work.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: schabi@logix-tt.com, synfinatic@gmail.com, jnasby@pervasive.com, matthew@zeut.net
Subject: Re: 10+hrs vs 15min because of just one index
In-Reply-To: <43EFA2D2.1050609@logix-tt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox