pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWhich is better Index
3+ messages / 3 participants
[nested] [flat]
* Which is better Index
@ 2011-04-05 10:26 Adarsh Sharma <adarsh.sharma@orkash.com>
0 siblings, 2 replies; 3+ messages in thread
From: Adarsh Sharma @ 2011-04-05 10:26 UTC (permalink / raw)
To: pgsql-performance
Dear all,
I want to clear my doubts regarding creating several single or a
multi-column indexes.
My table schema is :-
CREATE TABLE svo2( svo_id bigint NOT NULL DEFAULT
nextval('svo_svo_id_seq'::regclass), doc_id integer, sentence_id
integer, clause_id integer, negation integer, subject
charactervarying(3000), verb character varying(3000), "object"
character varying(3000), preposition character varying(3000),
subject_type character varying(3000), object_type
charactervarying(3000), subject_attribute character varying(3000),
object_attribute character varying(3000), verb_attribute character
varying(3000), subject_concept character varying(100), object_concept
character varying(100), subject_sense character varying(100),
object_sense character varying(100), subject_chain character
varying(5000), object_chain character varying(5000), sub_type_id
integer, obj_type_id integer, CONSTRAINT pk_svo_id PRIMARY KEY
(svo_id))WITH ( OIDS=FALSE);
_*Fore.g*_
CREATE INDEX idx_svo2_id_dummy ON svo2 USING btree (doc_id, clause_id,
sentence_id);
or
CREATE INDEX idx_svo2_id_dummy ON svo2 USING btree (doc_id);
CREATE INDEX idx_svo2_id_dummy1 ON svo2 USING btree (clause_id);
CREATE INDEX idx_svo2_id_dummy2 ON svo2 USING btree (sentence_id);
Which is better if a query uses all three columns in join where clause.
Thanks & best regards,
Adarsh Sharma
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Which is better Index
@ 2011-04-05 14:07 Chetan Suttraway <chetan.suttraway@enterprisedb.com>
parent: Adarsh Sharma <adarsh.sharma@orkash.com>
1 sibling, 0 replies; 3+ messages in thread
From: Chetan Suttraway @ 2011-04-05 14:07 UTC (permalink / raw)
To: Adarsh Sharma <adarsh.sharma@orkash.com>; +Cc: pgsql-performance
On Tue, Apr 5, 2011 at 3:56 PM, Adarsh Sharma <adarsh.sharma@orkash.com>wrote:
> Dear all,
>
> I want to clear my doubts regarding creating several single or a
> multi-column indexes.
> My table schema is :-
> CREATE TABLE svo2( svo_id bigint NOT NULL DEFAULT
> nextval('svo_svo_id_seq'::regclass), doc_id integer, sentence_id integer,
> clause_id integer, negation integer, subject charactervarying(3000), verb
> character varying(3000), "object" character varying(3000), preposition
> character varying(3000), subject_type character varying(3000), object_type
> charactervarying(3000), subject_attribute character varying(3000),
> object_attribute character varying(3000), verb_attribute character
> varying(3000), subject_concept character varying(100), object_concept
> character varying(100), subject_sense character varying(100), object_sense
> character varying(100), subject_chain character varying(5000),
> object_chain character varying(5000), sub_type_id integer, obj_type_id
> integer, CONSTRAINT pk_svo_id PRIMARY KEY (svo_id))WITH ( OIDS=FALSE);
>
>
> *Fore.g*
>
> CREATE INDEX idx_svo2_id_dummy ON svo2 USING btree (doc_id, clause_id,
> sentence_id);
>
> or
>
> CREATE INDEX idx_svo2_id_dummy ON svo2 USING btree (doc_id);
> CREATE INDEX idx_svo2_id_dummy1 ON svo2 USING btree (clause_id);
> CREATE INDEX idx_svo2_id_dummy2 ON svo2 USING btree (sentence_id);
>
> Which is better if a query uses all three columns in join where clause.
>
>
>
> Thanks & best regards,
> Adarsh Sharma
>
>
Thats very difficult to tell as you have not shared the details of system,
like what is the other table,
how the joined table are related and so on.
Basically we need to understand how the data is organized within table and
across schema or system.
To begin with, maybe below links could provide some insights:
http://www.postgresql.org/docs/current/static/indexes-multicolumn.html
http://www.postgresql.org/docs/current/static/indexes-bitmap-scans.html
--
Regards,
Chetan Suttraway
EnterpriseDB <http://www.enterprisedb.com/;, The Enterprise
PostgreSQL<http://www.enterprisedb.com/;
company.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Which is better Index
@ 2011-04-06 06:55 Greg Smith <greg@2ndQuadrant.com>
parent: Adarsh Sharma <adarsh.sharma@orkash.com>
1 sibling, 0 replies; 3+ messages in thread
From: Greg Smith @ 2011-04-06 06:55 UTC (permalink / raw)
To: pgsql-performance
On 04/05/2011 06:26 AM, Adarsh Sharma wrote:
> CREATE INDEX idx_svo2_id_dummy ON svo2 USING btree (doc_id,
> clause_id, sentence_id);
>
> or
>
> CREATE INDEX idx_svo2_id_dummy ON svo2 USING btree (doc_id);
> CREATE INDEX idx_svo2_id_dummy1 ON svo2 USING btree (clause_id);
> CREATE INDEX idx_svo2_id_dummy2 ON svo2 USING btree (sentence_id);
>
> Which is better if a query uses all three columns in join where clause.
Impossible to say. It's possible neither approach is best. If
clause_id and sentence_id are not very selective, the optimal setup here
could easily be an index on only doc_id. Just index that, let the query
executor throw out non-matching rows. Indexes are expensive to
maintain, and are not free to use in queries either.
What you could do here is create all four of these indexes, try to
simulate your workload, and see which actually get used. Throw out the
ones that the optimizer doesn't use anyway. The odds are against you
predicting what's going to happen accurately here. You might as well
accept that, set things up to measure what happens instead, and use that
as feedback on the design.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-04-06 06:55 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-05 10:26 Which is better Index Adarsh Sharma <adarsh.sharma@orkash.com>
2011-04-05 14:07 ` Chetan Suttraway <chetan.suttraway@enterprisedb.com>
2011-04-06 06:55 ` Greg Smith <greg@2ndQuadrant.com>
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