From: | "Peter A(dot) Daly" <petedaly(at)ix(dot)netcom(dot)com> |
---|---|
To: | Oleg Bartunov <oleg(at)sai(dot)msu(dot)su> |
Cc: | "Joshua b(dot) Jore" <josh(at)greentechnologist(dot)org>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Insight into indexes? (or inverting then externally) |
Date: | 2002-06-05 16:09:22 |
Message-ID: | 3CFE37B2.30901@ix.netcom.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
What's the performance like? I have a similar table (about 3 million
names, and I get great (sub second) performance when searching on a name
doing a select distinct name where name like 'smith john%'.
Have you run an EXPLAIN on the query you are doing? Is it even using
your indexes?
-Pete
Oleg Bartunov wrote:
>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
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Wieck | 2002-06-05 16:30:24 | Re: a trigger question |
Previous Message | Oliver Elphick | 2002-06-05 15:59:31 | Re: a trigger question |