Thread: query not using index

Hi,

I have created the following test table:
    create table history (event_id int8 not null, event_type int, event_name
char(10), event_ts timestamp, user_data_ref int8);

I've populated the table with 20,000,000 rows.

I have created an index table:
    create index history_idx on history (event_id);

When I issue the following query, it takes about 6 minutes:
    select event_id from history where event_id = 1;

I have run vacuum, vacuum analyze but it had no effect on the speed.
Explain sais the followings:

    explain select event_id from history where event_type = 120;
    NOTICE:  QUERY PLAN:

    Seq Scan on history  (cost=0.00..446079.00 rows=200000 width=8)

even if I do a drop index history_idx, explains sais the same.

Exactly the same thing happens if I create this table with event_id int8
primary key.

Since I don't think, that sequential scan is better then index scan in this
case,
could someone tell me why my index is not being used (- and still I'm
getting only 200000 rows scanned - )?

Thanks,
Norbert

>     explain select event_id from history where event_type = 120;
>     NOTICE:  QUERY PLAN:
>
>     Seq Scan on history  (cost=0.00..446079.00 rows=200000 width=8)
>
> even if I do a drop index history_idx, explains sais the same.
>

Try
    explain select event_id from history where event_type = 120::int8;

See
    http://fts.postgresql.org/db/mw/msg.html?mid=1031765
for some further explanation/history.

HTH,
-- Joe

Norbert Zoltan Toth writes:

> I have created the following test table:
>     create table history (event_id int8 not null, event_type int, event_name
> char(10), event_ts timestamp, user_data_ref int8);

> When I issue the following query, it takes about 6 minutes:
>     select event_id from history where event_id = 1;

This is a bit of a PostgreSQL bug.  See

http://www.de.postgresql.org/devel-corner/docs/postgres/datatype.html#DATATYPE-INT

Read the "Note:".

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

On Mon, 3 Sep 2001, Norbert Zoltan Toth wrote:

> Hi,
>
> I have created the following test table:
>     create table history (event_id int8 not null, event_type int, event_name
> char(10), event_ts timestamp, user_data_ref int8);
>
> I've populated the table with 20,000,000 rows.
>
> I have created an index table:
>     create index history_idx on history (event_id);
>
> When I issue the following query, it takes about 6 minutes:
>     select event_id from history where event_id = 1;
>
> I have run vacuum, vacuum analyze but it had no effect on the speed.
> Explain sais the followings:
>
>     explain select event_id from history where event_type = 120;
>     NOTICE:  QUERY PLAN:
>
>     Seq Scan on history  (cost=0.00..446079.00 rows=200000 width=8)
>
> even if I do a drop index history_idx, explains sais the same.

You need to make the constant be seen as an int8.  IIRC, you can either do
this by quoting the number '120' or by doing an explicit cast in the
query.  See past discussions on -general and -hackers in the archives.