Re: [PERFORM] Why does a simple query not use an obvious index?

2004-09-01 Thread Mr Pink
Hi Greg, Tom, etal It's true that oracle only peeks during a hard parse, and this can have good or bad results depending on the situation. Basically, the first value used in that query will determine the plan until that query is bumped from the sql cache or the server is restarted. As far as I

Re: [PERFORM] Why does a simple query not use an obvious index?

2004-08-30 Thread Pierre-Frdric Caillaud
Another primary key trick : If you insert records with a serial primary key, and rarely delete them or update the timestamp, you can use the primary key to compute an approximate number of rows. a := SELECT pkey FROM table WHERE timestamp() threshold ORDER BY timestamp ASC LIMIT 1;

Re: [PERFORM] Why does a simple query not use an obvious index?

2004-08-30 Thread Greg Stark
[I'm actually responding to the previous post from Tom Lane, but I've deleted it and the archives seem to be down again.] The assumption being made is that the first provided result is representative of all future results. I don't see any reason that making this assumption of all stable

Re: [PERFORM] Why does a simple query not use an obvious index?

2004-08-30 Thread Tom Lane
Greg Stark [EMAIL PROTECTED] writes: However I have the complementary reaction. I find peeking at the first bind parameter to be scary as hell. Functions seem slightly less scary. FWIW, we only do it in the context of unnamed parameterized queries. As the protocol docs say, those are optimized

Re: [PERFORM] Why does a simple query not use an obvious index?

2004-08-29 Thread Mr Pink
Strangely enough, I don't find that result surprising. if the vast bulk of the data is in the past and now()-60 represents a very small slice of the data we might expect that using an index is optimal, but there could be many reasons why it doesn't get used. AFAIK postgres doesn't peek at

Re: [PERFORM] Why does a simple query not use an obvious index?

2004-08-29 Thread Scott Marlowe
On Fri, 2004-08-27 at 11:12, Jack Kerkhof wrote: The query: select count(*) from billing where timestamp now()-60 should obviously use the index CREATE INDEX billing_timestamp_idx ON billing USING btree (timestamp timestamp_ops); on a table with 140 rows. But it uses

Re: [PERFORM] Why does a simple query not use an obvious index?

2004-08-29 Thread Steinar H. Gunderson
On Sun, Aug 29, 2004 at 11:04:48AM -0700, Mr Pink wrote: Another is that if the condition data types don't match then an indes won't be used you could try: select count(*) from billing where timestamp (now()-60)::timestamp In fact, I've had success with code like select count(*)

Re: [PERFORM] Why does a simple query not use an obvious index?

2004-08-29 Thread Greg Stark
Mr Pink [EMAIL PROTECTED] writes: AFAIK postgres doesn't peek at values used in a query when optimizing Of course it does. However sometimes things don't work perfectly. To get good answers rather than just guesses we'll need two things: . What version of postgres are you using. . The

Re: [PERFORM] Why does a simple query not use an obvious index?

2004-08-29 Thread Scott Marlowe
On Sun, 2004-08-29 at 15:12, Greg Stark wrote: Scott Marlowe [EMAIL PROTECTED] writes: Also, count(*) is likely to always generate a seq scan due to the way aggregates are implemented currently in pgsql. you might want to try: Huh? I'm curious to know what you're talking about here.

Re: [PERFORM] Why does a simple query not use an obvious index?

2004-08-29 Thread Scott Marlowe
On Sun, 2004-08-29 at 15:38, Scott Marlowe wrote: On Sun, 2004-08-29 at 15:12, Greg Stark wrote: Scott Marlowe [EMAIL PROTECTED] writes: Also, count(*) is likely to always generate a seq scan due to the way aggregates are implemented currently in pgsql. you might want to try: