index and queries using '<' '>'

[Marc Boucher]

I'm using PG 7.3.4

I've a table with a column of type int8 where I store date-based values,
and an index exists for it.
The problem is that the index is almost never used with the '>' test.

# explain SELECT date FROM album WHERE (date='10939896 00');
Index Scan using date_album_key on album (cost=0.00..86. 31 rows=21 width=8)
Index Cond: (date = 1093989600::big int)

# explain SELECT date FROM album WHERE (date>'10939896 00');
Seq Scan on album (cost=0.00..907 .91 rows=447 width=8)
Filter: (date > 1093989600::big int)

# explain SELECT date FROM album WHERE (date>'10999896 00');
Index Scan using date_album_key on album (cost=0.00..323 .09 rows=84 width=8)
Index Cond: (date > 1099989600::big int)


It works when the query is supposed to generate low number of rows. The
problem is that the execution time is much longer with a scan.
How can I force the use of this index?


--
Marc


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Martijn van Oosterhout]

Re: index and queries using '&lt;' '&gt;'

The system seems to think that a scan is cheap because the table is so
small. Have you ever ANALYZEd that table? Also, EXPLAIN ANALYZE gives a
much better idea of what is going on...

On Thu, Nov 18, 2004 at 03:55:12PM +0100, Marc Boucher wrote:[color=blue]
> I'm using PG 7.3.4
>
> I've a table with a column of type int8 where I store date-based values,
> and an index exists for it.
> The problem is that the index is almost never used with the '>' test.
>
> # explain SELECT date FROM album WHERE (date='10939896 00');
> Index Scan using date_album_key on album (cost=0.00..86. 31 rows=21 width=8)
> Index Cond: (date = 1093989600::big int)
>
> # explain SELECT date FROM album WHERE (date>'10939896 00');
> Seq Scan on album (cost=0.00..907 .91 rows=447 width=8)
> Filter: (date > 1093989600::big int)
>
> # explain SELECT date FROM album WHERE (date>'10999896 00');
> Index Scan using date_album_key on album (cost=0.00..323 .09 rows=84width=8)
> Index Cond: (date > 1099989600::big int)
>
>
> It works when the query is supposed to generate low number of rows. The
> problem is that the execution time is much longer with a scan.
> How can I force the use of this index?
>
>
> --
> Marc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster[/color]

--
Martijn van Oosterhout <kleptog@svana. org> http://svana.org/kleptog/[color=blue]
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.[/color]

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBnL5UY5T wig3Ge+YRApEvAJ 9A3dE02QtemBgVX T0PpC9NF5ScKwCf Vu00
lPlnHbWNixpDwEX YL613crE=
=3Oy3
-----END PGP SIGNATURE-----