Speeding query
Hi!
I have 2 tables
\d table1
Column | Type
----------------+--------------------------
id_entry | integer
created | timestamp with time zone
title | character varying(64)
description | text
Primary key: table1_pk
Index "table1_pk"
Column | Type
----------+---------
id_entry | integer
unique btree (primary key)
\d table2
Column | Type
-------------+-----------------------
id_category | character varying(64)
id_entry | character varying(64)
Primary key: table2_pk
\d table2_pk
Column | Type
-------------+-----------------------
id_category | character varying(64)
id_entry | character varying(64)
unique btree (primary key)
Not i have query like this
select d.title from table1 d, table2 c where c.id_category='09' and d.id_entry=c.id_entry;
This is query plan
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00..154844.08 rows=96 width=36)
-> Seq Scan on table1 d (cost=0.00..2957.36 rows=25236 width=28)
-> Index Scan using table1_pk on table2 c (cost=0.00..6.00 rows=1 width=8)
I see that there is seq scan over 25k rows, how can i set
indexes to make query faster and use indexes.
--
lp,
Uros mailto:uros.gruber@sir-mag.com
On Wed, 2002-04-24 at 16:54, Uros Gruber wrote:
select d.title from table1 d, table2 c where c.id_category='09' and d.id_entry=c.id_entry;
I see that there is seq scan over 25k rows, how can i set
indexes to make query faster and use indexes.
try setting an index on table2's id_entry. your index on table2 is a
compound index with id_entry as the second field; so no indexes match
just table2's id_entry.
--
Tom Jenkins
Development InfoStructure
http://www.devis.com
Someone needed to add a serial type to a table. Here's the quick dirty,
lazy dba way:
say table t1 has a structure like so:
create table t1 (field1 text, id int);
and data in it, and we want id to be a serial (autoincrementing etc...)
field.
do this:
create table t2 (field1 text, id serial);
Now, assuming that all the data in t1 has a unique id, we can just do
this:
insert into t2 (select * from t1);
and voila, our table is populated. One small problem, the current value
of the associate sequence is still set to the original number (1 I think).
So, we do this:
select setval('t2_id_seq',(select max(id) from t2));
And now we have our sequence ready to go.
Good luck!