pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL using the wrong Index
6+ messages / 4 participants
[nested] [flat]
* PostgreSQL using the wrong Index
@ 2005-06-13 13:02 Alex Stapleton <alexs@advfn.com>
2005-06-13 13:08 ` Re: PostgreSQL using the wrong Index Alex Stapleton <alexs@advfn.com>
2005-06-13 14:54 ` Re: PostgreSQL using the wrong Index Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Alex Stapleton @ 2005-06-13 13:02 UTC (permalink / raw)
To: pgsql-performance
We have two index's like so
l1_historical=# \d "N_intra_time_idx"
Index "N_intra_time_idx"
Column | Type
--------+-----------------------------
time | timestamp without time zone
btree
l1_historical=# \d "N_intra_pkey"
Index "N_intra_pkey"
Column | Type
--------+-----------------------------
symbol | text
time | timestamp without time zone
unique btree (primary key)
and on queries like this
select * from "N_intra" where symbol='SOMETHING WHICH DOESNT EXIST'
order by time desc limit 1;
PostgreSQL takes a very long time to complete, as it effectively
scans the entire table, backwards. And the table is huge, about 450
million rows. (btw, there are no triggers or any other exciting
things like that on our tables in this db.)
but on things where the symbol does exist in the table, it's more or
less fine, and nice and fast.
Whilst the option the planner has taken might be faster most of the
time, the worst case scenario is unacceptable for obvious reasons.
I've googled for trying to force the use of a specific index, but
can't find anything relevant. Does anyone have any suggestions on
getting it to use an index which hopefully will have better worst
case performance?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL using the wrong Index
2005-06-13 13:02 PostgreSQL using the wrong Index Alex Stapleton <alexs@advfn.com>
@ 2005-06-13 13:08 ` Alex Stapleton <alexs@advfn.com>
2005-06-13 14:47 ` Re: PostgreSQL using the wrong Index John A Meinel <john@arbash-meinel.com>
1 sibling, 1 reply; 6+ messages in thread
From: Alex Stapleton @ 2005-06-13 13:08 UTC (permalink / raw)
To: Alex Stapleton <alexs@advfn.com>; +Cc: pgsql-performance
Oh, we are running 7.4.2 btw. And our random_page_cost = 1
On 13 Jun 2005, at 14:02, Alex Stapleton wrote:
> We have two index's like so
>
> l1_historical=# \d "N_intra_time_idx"
> Index "N_intra_time_idx"
> Column | Type
> --------+-----------------------------
> time | timestamp without time zone
> btree
>
>
> l1_historical=# \d "N_intra_pkey"
> Index "N_intra_pkey"
> Column | Type
> --------+-----------------------------
> symbol | text
> time | timestamp without time zone
> unique btree (primary key)
>
> and on queries like this
>
> select * from "N_intra" where symbol='SOMETHING WHICH DOESNT EXIST'
> order by time desc limit 1;
>
> PostgreSQL takes a very long time to complete, as it effectively
> scans the entire table, backwards. And the table is huge, about 450
> million rows. (btw, there are no triggers or any other exciting
> things like that on our tables in this db.)
>
> but on things where the symbol does exist in the table, it's more
> or less fine, and nice and fast.
>
> Whilst the option the planner has taken might be faster most of the
> time, the worst case scenario is unacceptable for obvious reasons.
> I've googled for trying to force the use of a specific index, but
> can't find anything relevant. Does anyone have any suggestions on
> getting it to use an index which hopefully will have better worst
> case performance?
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL using the wrong Index
2005-06-13 13:02 PostgreSQL using the wrong Index Alex Stapleton <alexs@advfn.com>
2005-06-13 13:08 ` Re: PostgreSQL using the wrong Index Alex Stapleton <alexs@advfn.com>
@ 2005-06-13 14:47 ` John A Meinel <john@arbash-meinel.com>
2005-06-13 15:20 ` Re: PostgreSQL using the wrong Index Alex Stapleton <alexs@advfn.com>
0 siblings, 1 reply; 6+ messages in thread
From: John A Meinel @ 2005-06-13 14:47 UTC (permalink / raw)
To: Alex Stapleton <alexs@advfn.com>; +Cc: pgsql-performance
Alex Stapleton wrote:
> Oh, we are running 7.4.2 btw. And our random_page_cost = 1
>
Which is only correct if your entire db fits into memory. Also, try
updating to a later 7.4 version if at all possible.
> On 13 Jun 2005, at 14:02, Alex Stapleton wrote:
>
>> We have two index's like so
>>
>> l1_historical=# \d "N_intra_time_idx"
>> Index "N_intra_time_idx"
>> Column | Type
>> --------+-----------------------------
>> time | timestamp without time zone
>> btree
>>
Just so you are aware, writing this as: "We have an index on
N_intra(time) and one on N_Intra(symbol, time)" is a lot more succinct.
>>
>> l1_historical=# \d "N_intra_pkey"
>> Index "N_intra_pkey"
>> Column | Type
>> --------+-----------------------------
>> symbol | text
>> time | timestamp without time zone
>> unique btree (primary key)
>>
>> and on queries like this
>>
>> select * from "N_intra" where symbol='SOMETHING WHICH DOESNT EXIST'
>> order by time desc limit 1;
>>
>> PostgreSQL takes a very long time to complete, as it effectively
>> scans the entire table, backwards. And the table is huge, about 450
>> million rows. (btw, there are no triggers or any other exciting
>> things like that on our tables in this db.)
>>
>> but on things where the symbol does exist in the table, it's more or
>> less fine, and nice and fast.
>
What happens if you do:
SELECT * FROM "N_intra" WHERE symbol='doesnt exist' ORDER BY symbol,
time DESC LIMIT 1;
Yes, symbol is constant, but it frequently helps the planner realize it
can use an index scan if you include all terms in the index in the ORDER
BY clause.
>>
>> Whilst the option the planner has taken might be faster most of the
>> time, the worst case scenario is unacceptable for obvious reasons.
>> I've googled for trying to force the use of a specific index, but
>> can't find anything relevant. Does anyone have any suggestions on
>> getting it to use an index which hopefully will have better worst
>> case performance?
>
Try the above first. You could also create a new index on symbol
CREATE INDEX "N_intra_symbol_idx" ON "N_intra"(symbol);
Then the WHERE clause should use the symbol index, which means it can
know quickly that an entry doesn't exist. I'm not sure how many entries
you have per symbol, though, so this might cause problems in the ORDER
BY time portion.
I'm guessing what you really want is to just do the ORDER BY symbol, time.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL using the wrong Index
2005-06-13 13:02 PostgreSQL using the wrong Index Alex Stapleton <alexs@advfn.com>
2005-06-13 13:08 ` Re: PostgreSQL using the wrong Index Alex Stapleton <alexs@advfn.com>
2005-06-13 14:47 ` Re: PostgreSQL using the wrong Index John A Meinel <john@arbash-meinel.com>
@ 2005-06-13 15:20 ` Alex Stapleton <alexs@advfn.com>
0 siblings, 0 replies; 6+ messages in thread
From: Alex Stapleton @ 2005-06-13 15:20 UTC (permalink / raw)
To: John A Meinel <john@arbash-meinel.com>; +Cc: pgsql-performance
On 13 Jun 2005, at 15:47, John A Meinel wrote:
> Alex Stapleton wrote:
>
>
>> Oh, we are running 7.4.2 btw. And our random_page_cost = 1
>>
>>
> Which is only correct if your entire db fits into memory. Also, try
> updating to a later 7.4 version if at all possible.
>
I am aware of this, I didn't configure this machine though
unfortuantely.
>> On 13 Jun 2005, at 14:02, Alex Stapleton wrote:
>>
>>
>>> We have two index's like so
>>>
>>> l1_historical=# \d "N_intra_time_idx"
>>> Index "N_intra_time_idx"
>>> Column | Type
>>> --------+-----------------------------
>>> time | timestamp without time zone
>>> btree
>>>
>>>
> Just so you are aware, writing this as: "We have an index on
> N_intra(time) and one on N_Intra(symbol, time)" is a lot more
> succinct.
>
Sorry, I happened to have them there in my clipboard at the time so I
just blindly pasted them in.
>>>
>>> l1_historical=# \d "N_intra_pkey"
>>> Index "N_intra_pkey"
>>> Column | Type
>>> --------+-----------------------------
>>> symbol | text
>>> time | timestamp without time zone
>>> unique btree (primary key)
>>>
>>> and on queries like this
>>>
>>> select * from "N_intra" where symbol='SOMETHING WHICH DOESNT EXIST'
>>> order by time desc limit 1;
>>>
>>> PostgreSQL takes a very long time to complete, as it effectively
>>> scans the entire table, backwards. And the table is huge, about 450
>>> million rows. (btw, there are no triggers or any other exciting
>>> things like that on our tables in this db.)
>>>
>>> but on things where the symbol does exist in the table, it's
>>> more or
>>> less fine, and nice and fast.
>>>
>>
>>
> What happens if you do:
> SELECT * FROM "N_intra" WHERE symbol='doesnt exist' ORDER BY symbol,
> time DESC LIMIT 1;
Hurrah! I should of thought of this, considering i've done it in the
past :) Thanks a lot, that's great.
> Yes, symbol is constant, but it frequently helps the planner
> realize it
> can use an index scan if you include all terms in the index in the
> ORDER
> BY clause.
>
>>>
>>> Whilst the option the planner has taken might be faster most of the
>>> time, the worst case scenario is unacceptable for obvious reasons.
>>> I've googled for trying to force the use of a specific index, but
>>> can't find anything relevant. Does anyone have any suggestions on
>>> getting it to use an index which hopefully will have better worst
>>> case performance?
>>>
>>
>>
> Try the above first. You could also create a new index on symbol
> CREATE INDEX "N_intra_symbol_idx" ON "N_intra"(symbol);
>
> Then the WHERE clause should use the symbol index, which means it can
> know quickly that an entry doesn't exist. I'm not sure how many
> entries
> you have per symbol, though, so this might cause problems in the ORDER
> BY time portion.
>
> I'm guessing what you really want is to just do the ORDER BY
> symbol, time.
>
> John
> =:->
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL using the wrong Index
2005-06-13 13:02 PostgreSQL using the wrong Index Alex Stapleton <alexs@advfn.com>
@ 2005-06-13 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-13 15:47 ` Re: PostgreSQL using the wrong Index Wei Weng <wweng@kencast.com>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2005-06-13 14:54 UTC (permalink / raw)
To: Alex Stapleton <alexs@advfn.com>; +Cc: pgsql-performance
Alex Stapleton <alexs@advfn.com> writes:
> l1_historical=# \d "N_intra_pkey"
> Index "N_intra_pkey"
> Column | Type
> --------+-----------------------------
> symbol | text
> time | timestamp without time zone
> unique btree (primary key)
> and on queries like this
> select * from "N_intra" where symbol='SOMETHING WHICH DOESNT EXIST'
> order by time desc limit 1;
This was just covered in excruciating detail yesterday ...
You need to write
order by symbol desc, time desc limit 1
to get the planner to recognize the connection to the sort order
of this index. Since you're only selecting one value of symbol,
the actual output doesn't change.
> Oh, we are running 7.4.2 btw. And our random_page_cost = 1
I'll bet lunch that that is a bad selection of random_page_cost,
unless your database is so small that it all fits in RAM.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: PostgreSQL using the wrong Index
2005-06-13 13:02 PostgreSQL using the wrong Index Alex Stapleton <alexs@advfn.com>
2005-06-13 14:54 ` Re: PostgreSQL using the wrong Index Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-06-13 15:47 ` Wei Weng <wweng@kencast.com>
0 siblings, 0 replies; 6+ messages in thread
From: Wei Weng @ 2005-06-13 15:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alex Stapleton <alexs@advfn.com>; pgsql-performance
Tom Lane wrote:
>
>
> This was just covered in excruciating detail yesterday ...
>
> You need to write
> order by symbol desc, time desc limit 1
> to get the planner to recognize the connection to the sort order
> of this index. Since you're only selecting one value of symbol,
> the actual output doesn't change.
>
Is this the right behavior (not a bug)? Is postgresql planning on changing
this soon?
Thanks
Wei
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-06-13 15:47 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-13 13:02 PostgreSQL using the wrong Index Alex Stapleton <alexs@advfn.com>
2005-06-13 13:08 ` Alex Stapleton <alexs@advfn.com>
2005-06-13 14:47 ` John A Meinel <john@arbash-meinel.com>
2005-06-13 15:20 ` Alex Stapleton <alexs@advfn.com>
2005-06-13 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-13 15:47 ` Wei Weng <wweng@kencast.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox