Re: Insight into indexes? (or inverting then externally)

Joshua,

what kind of queries do you need ? Do you need to perform full text search ?

Oleg
On Wed, 5 Jun 2002, Joshua b. Jore wrote:

> So far as I can tell indexes in PostgreSQL are fairly opaque things. The
> planner may or may not decide to use them, exactly what they contain is
> largely unknown (unless I want to read the source), how PostgreSQL uses
> them and in which cases a btree index is better than a hash index.
>
> Foo. If I've managed to just skip over a manual section please whack me
> with a 2x4. If I haven't then any links would be appreciated. I'm
> considering how best to provide a fast index on a 2.5 million row table
> (all the voters in Minnesota). Since I can't see what PostgreSQL is doing
> with the indexes I have to wonder if I can eke out some more performance
> by just indexing the record's id numbers (a usually numeric char(10) -
> blame the MN Secretary of State for the "mostly" part) in PostgreSQL and
> then sticking an inverted index into say... BerkeleyDB.
>
> Records might look like where it's obvious that the ids store nicely..
>
> lname.db
> 'Jore' => '12345678901234567890'
>
> So before I jump down this rabbit hole is there anything I can do to
> PostgreSQL to see what is in the indexes and if there is any way to tweak
> them? And when is a hash index preferrable to a btree index? Or that other
> index type that I don't remember the name of.
>
> Joshua b. Jore ; http://www.greentechnologist.org ; 1121 1233 1311 200
> 1201 1302 1211 200 1201 1303 200 1300 1233 1313 1211 1302 1212 1311 1230
> 200 1201 1303 200 1321 1233 1311 1302 200 1211 1232 1211 1231 1321 200
> 1310 1220 1221 1232 1223 1303 200 1321 1233 1311 200 1201 1302 1211 232
> 200 1112 1233 1310 1211 200 1013 1302 1211 1211 1232 201 22
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83