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='1093989600');
Index Scan using date_album_key on album (cost=0.00..86.31 rows=21 width=8)
Index Cond: (date = 1093989600::bigint)

# explain SELECT date FROM album WHERE (date>'1093989600');
Seq Scan on album (cost=0.00..907.91 rows=447 width=8)
Filter: (date > 1093989600::bigint)

# explain SELECT date FROM album WHERE (date>'1099989600');
Index Scan using date_album_key on album (cost=0.00..323.09 rows=84 width=8)
Index Cond: (date > 1099989600::bigint)


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

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:
--
Martijn van Oosterhout <kleptog (AT) svana (DOT) org> http://svana.org/kleptog/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBnL5UY5Twig3Ge+YRApEvAJ9A3dE02QtemBgVXT0PpC 9NF5ScKwCfVu00
lPlnHbWNixpDwEXYL613crE=
=3Oy3
-----END PGP SIGNATURE-----

At 16:23 18/11/2004 +0100, Martijn van Oosterhout wrote:
small part of it.

I have also tried ALTER TABLE ALTER COLUMN SET STATISTICS, but without
improvement.



Since the table is still growing, and the amount of rows in the reply of
the query is quite uniform (it's not dependant on the size of the table), I
hope that the statistics will evolve in a state that will force the use of
the index.


thanks

--
Marc


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

On Fri, Nov 19, 2004 at 14:45:04 +0100,
Marc Boucher <pgml (AT) gmx (DOT) net> wrote:
No, small is typically less than 1%. This depends on the size of the rows
and how much better accessing disk blocks sequentially is in your
enviroment and the size of your cache.

Because your table is so small it will probably all be cached after being
read through once, so you may want to tune your config settings to
say than random disk access costs only a little more than sequential
access. However, you need to be careful if your table is going to grow
a lot larger.

Index scans aren't always faster than sequential scans.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

At 08:16 19/11/2004 -0600, Bruno Wolff III wrote:
"problem". With modern hard drives, sequential scan could be faster.

tables can.


advised me to try "set enable_seqscan=off;". It takes 50-60% (after
checking right now) less to use the index. Unfortunately I can't use this
setting, the query being part of a larger query (joins), and the time
gained on this particular index is partially lost on the joins.


--
Marc


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

On Fri, Nov 19, 2004 at 15:47:21 +0100,
Marc Boucher <pgml (AT) gmx (DOT) net> wrote:
Tweaking random_page_cost may help. The default is 4, which is often too
high. It is supposed to represent the relative cost of retrieving disk
blocks in a random order as compared to retrieving them in sequential
order. Lowering this value will make index scans look better. Than value
should not be lowered below 1.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On Fri, 19 Nov 2004, Marc Boucher wrote:

EXPLAIN ANALYZE is similar to EXPLAIN except that it runs the query
and gives real time per step and real row counts rather than the
estimates. It's very handy.

If random page reads are consistently less expensive than postgres
currently thinks, you may want to lower random_page_cost. EXPLAIN ANALYZE
is useful here because it lets you compare the real times versus the
estimated costs of both scan types when used with enable_seqscan=off.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Marc Boucher <pgml (AT) gmx (DOT) net> writes:
A less brute-force way of encouraging the planner to use indexscans is
to reduce the random_page_cost setting. It defaults to 4 but many
people find that values nearer 2 are more representative of what happens
in their environments.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly