From: Steve Midgley <public@misuse.org>
To: pgsql-sql@postgresql.org
Subject: SQL performance help: self join or static var
Date: Mon, 17 Sep 2007 17:42:27 -0700
Message-ID: <20070918004246.87CBC9F99EC@postgresql.org> (raw)
Hi,
Given a table (truncated some real fields for simplicity):
CREATE TABLE city
(
id serial NOT NULL,
muni_city_id integer,
post_code_city_id integer,
alias_city_id integer,
city_type character varying(15),
post_code_type character varying(15),
CONSTRAINT city_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;
CREATE INDEX index_city_on_muni_city_id
ON city
USING btree
(muni_city_id);
CREATE INDEX index_city_on_post_code_type
ON city
USING btree
(post_code_type);
Filled with ~168,000 records
Which of the following SQL statements should I expect better
performance on?
select * from city
where post_code_type in ('P', 'R')
EXPLAIN ANALYZE:
"Seq Scan on city (cost=0.00..4492.82 rows=76172 width=290) (actual
time=0.039..163.564 rows=30358 loops=1)"
" Filter: ((post_code_type)::text = ANY (('{P,R}'::character
varying[])::text[]))"
"Total runtime: 231.947 ms"
OR
select * from city
where id = muni_city_id
EXPLAIN ANALYZE:
"Seq Scan on city (cost=0.00..3535.41 rows=383 width=290) (actual
time=0.022..124.463 rows=30200 loops=1)"
" Filter: (muni_city_id = id)"
"Total runtime: 195.342 ms"
In my case both statements are semantically equivalent and I'm trying
to figure out if I should prefer the search of a varchar field for
static values over the "self join" constraint to an indexed integer
column?
My (almost completely ignorant) eyes say that the latter
(id=muni_city_id) is faster by a little - any voices of support or
other ideas in this regard? Caveats?
Thanks,
Steve
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-sql@postgresql.org
Cc: public@misuse.org
Subject: Re: SQL performance help: self join or static var
In-Reply-To: <20070918004246.87CBC9F99EC@postgresql.org>
* 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