473,549 Members | 2,553 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Performance with different index types

Hello,

are there any experiences about the performance of indices
with different data types.

How do the performance of an index that consists of

- an integer field
- a varchar() field
- a text field

differ?

Is it a waste of memory/performance to make a text field
primary key?

Thanks,

Hans
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #1
1 3422
On Mon, 18 Aug 2003, Johann Uhrmann wrote:
Hello,

are there any experiences about the performance of indices
with different data types.

How do the performance of an index that consists of

- an integer field
- a varchar() field
- a text field

differ?
It's not so much about the field type as what you are doing with it.
Btree indexes are the default, and often are your best choice. Rtree
indexes are often used for spatial comparisons (i.e. is this point inside
this polygon stuff). Hash indexes should be a win for certain types of
problems, but their implementation is pretty slow in postgresql, so you're
often still better off with an Rtree index.

GiST seems like it may replace Rtree indexes at some future date, but
they're still kind of in development.

If you need multi-column indexes, you have to use either btree or gist.

The standard rule of thumb is, when in doubt, use btree. :-)

also, look into partial / functional indexes. For instance, if you have a
column that's a bool with 3 million rows, and <100 of those rows have the
bool set to true, while the others are all false, it might make sense to
create a partial index on that field for when you want one of those 100
rows with that field set to true:

create index abc123 on tableabc (bool_field) where bool_field IS TRUE.

the other issue folks have when they start using postgresql is that it
sometimes tends to seq scan when you think it should be using the index.
It may well be that a seq scan is a better choice, but often it's not, and
the query planny just doesn't have enough information to know that.

so, you need to vacuum, analyze, and possibly edit your postgresql.conf
file's random_page_cos t, effective_cache _size, and a few other fields to
give the planner a kick in the right direction.

the final issue is the one of type mismatch. If you've got a field with
an int8, and you do this:

select * from table where int8field=123;

the planner may not use your index on int8field, since 123 gets coerced to
int4. You need to cast the 123 to int in one of a few ways:

select * from table where int8field=cast (123 as int8); <- SQL spec way
select * from table where int8field=123:: int8;
select * from table where int8field='123' ;
Is it a waste of memory/performance to make a text field
primary key?


Well, that depends. If the text field is the NATURAL key, and you'll
likely want to refer to it from other tables, then it's often a good
choice, semantically at least, to use it as a pk.

Sometimes, though, you need better performance, and then you can use an
artificial pk, like a serial column, and create a unique index on the
"natural" key column (i.e. the text field) to make sure it stays unique,
but use the serial column for all table joins and such.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 11 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

18
8742
by: zebi | last post by:
hello, What's your opinion : The best type of temporary tablespace (DB2V7.2 SP 7 in AIX 4.3 / multiprocessor ) is DMS ou SMS ? Thanks ZEB
115
7506
by: Mark Shelor | last post by:
I've encountered a troublesome inconsistency in the C-language Perl extension I've written for CPAN (Digest::SHA). The problem involves the use of a static array within a performance-critical transform function. When compiling under gcc on my big-endian PowerPC (Mac OS X), declaring this array as "static" DECREASES the transform throughput by...
12
3188
by: wxs | last post by:
Many times we have a bunch of enums we have from either different enums or the same enum that will have various numeric values assigned. Rarely will there be collisions in numbering between the enums. These enums you might imagine would be like OrderPrice=27, OrderQuantity=50, OrderSide=62. There may be a lot of these. So normally what we...
19
3131
by: Tom Jastrzebski | last post by:
Hello, I was just testing VB.Net on Framework.Net 2.0 performance when I run into the this problem. This trivial code attached below executed hundreds, if not thousand times faster in VB 6.0 than in .Net environment, under VS 2005 Beta 2. Does anyone have any idea whether this will be addressed in the final release? Thanks, Tomasz
48
4419
by: Alex Chudnovsky | last post by:
I have come across with what appears to be a significant performance bug in ..NET 2.0 ArrayList.Sort method when compared with Array.Sort on the same data. Same data on the same CPU gets sorted a lot faster with both methods using .NET 1.1, that's why I am pretty sure its a (rather serious) bug. Below you can find C# test case that should...
2
1596
by: rAinDeEr | last post by:
Hi, I have a table with the following definition. The primary key is west_nme and west_eff_tms and i have created unique index on them. The foreign keys which reference other parent tables are west_typ_cde,r_cde,ccl_cde and t_cde and I have created indexes FK_t_west01,FK_t_west02,FK_t_west03 respectively. Now, all the other fields in...
10
4264
by: shsandeep | last post by:
The ETL application loaded around 3000 rows in 14 seconds in a Development database while it took 2 hours to load in a UAT database. UAT db is partitioned. Dev db is not partitioned. the application looks for existing rows in the table...if they already exist then it updates otherwise inserts them. The table is pretty large, around 6.5...
9
5745
by: HC | last post by:
Hello, all, I started out thinking my problems were elsewhere but as I have worked through this I have isolated my problem, currently, as a difference between MSDE and SQL Express 2005 (I'll just call it Express for simplicity). I have, to try to simplify things, put the exact same DB on two systems, one running MSDE and one running...
13
4583
by: atlaste | last post by:
Hi, I'm currently developing an application that uses a lot of computational power, disk access and memory caching (to be more exact: an information retrieval platform). In these kind of applications the last thing that remains is bare performance tuning. So for example, you can do an 'if then else' on a bit like a 'case/ switch', an...
0
7457
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7723
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7965
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6051
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
5092
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3504
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1949
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1063
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
771
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.