Обсуждение: 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!