Thread: Timestamp indicies not being used!

Timestamp indicies not being used!

From
Pedro Doria Meunier
Date:
Hi All,

I *really* need some help with this one...

I have a table ~18M rows with a 'timestamp with time zone' column. It's
indexed thus:

CREATE INDEX my_table_timestamp_idx
  ON my_table
  USING btree
  (zulu_timestamp);

whenever I issue a command like:
SELECT speed, digital_input_1, digital_input_2, digital_input_3,
digital_input_4, priority FROM my_table WHERE id='123456789012345'
AND zulu_timestamp < '2009-07-10 15:24:45+01'
ORDER BY zulu_timestamp DESC LIMIT 1

it takes FOREVER in instances where there's only 1 row or 0 rows in the
table

the column id is also indexed.

this is the query plan:

"Limit  (cost=0.00..83.88 rows=1 width=20) (actual
time=810784.212..810784.212 rows=0 loops=1)"
"  ->  Index Scan Backward using my_table_timestamp_idx on my_table
(cost=0.00..3706639.95 rows=44192 width=20) (actual
time=810784.210..810784.210 rows=0 loops=1)"
"        Index Cond: (zulu_timestamp < '2009-07-10
15:24:45+01'::timestamp with time zone)"
"        Filter: (id = '123456789012345'::bpchar)"
"Total runtime: 810808.298 ms"


Since there are hundreds of devices delivering their data payload to the
my_table these queries effectively block other and take postgresql to a
screeching halt ... :-(

Could someone PLEASE tell me how can I solve this?


Thanks in advance,

--
Pedro Doria Meunier
GSM: +351 96 17 20 188
Skype: pdoriam



Re: Timestamp indicies not being used!

From
Andreas Kretschmer
Date:
Pedro Doria Meunier <pdoria@netmadeira.com> wrote:

> Hi All,
>
> I *really* need some help with this one...
>
> I have a table ~18M rows with a 'timestamp with time zone' column. It's
> indexed thus:
>
> CREATE INDEX my_table_timestamp_idx
>   ON my_table
>   USING btree
>   (zulu_timestamp);
>
> whenever I issue a command like:
> SELECT speed, digital_input_1, digital_input_2, digital_input_3,
> digital_input_4, priority FROM my_table WHERE id='123456789012345'
> AND zulu_timestamp < '2009-07-10 15:24:45+01'
> ORDER BY zulu_timestamp DESC LIMIT 1
>
> it takes FOREVER in instances where there's only 1 row or 0 rows in the
> table
>
> the column id is also indexed.
>
> this is the query plan:
>
> "Limit  (cost=0.00..83.88 rows=1 width=20) (actual
> time=810784.212..810784.212 rows=0 loops=1)"
> "  ->  Index Scan Backward using my_table_timestamp_idx on my_table
> (cost=0.00..3706639.95 rows=44192 width=20) (actual
> time=810784.210..810784.210 rows=0 loops=1)"
> "        Index Cond: (zulu_timestamp < '2009-07-10
> 15:24:45+01'::timestamp with time zone)"
> "        Filter: (id = '123456789012345'::bpchar)"
> "Total runtime: 810808.298 ms"

There is a BIG difference between estimated and real rows (rows=44192
vs. rows=0. Try to increase the statistics for this column (and of
course, run analyse for this table)

Which PG-Version? And show us the table-definition (for the id-column).


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Timestamp indicies not being used!

From
Sam Mason
Date:
On Sun, Jul 19, 2009 at 11:15:39AM +0100, Pedro Doria Meunier wrote:
> I have a table ~18M rows with a 'timestamp with time zone' column. It's
> indexed thus:
>
> CREATE INDEX my_table_timestamp_idx
>   ON my_table
>   USING btree
>   (zulu_timestamp);

Based on your query, I think you want a multi-column index---probably on
(id,zulu_timestamp).

The problem with just having an index on either column is that it's
difficult to combine them and PG hence just thinks that it will be
easier to scan backwards in time looking for the first entry for the
identifier you specified.

BTW, if you're concerned about insert performance then the less indexes
you have the better.

--
  Sam  http://samason.me.uk/

Re: Timestamp indicies not being used!

From
Andreas Kretschmer
Date:
Sam Mason <sam@samason.me.uk> wrote:

> On Sun, Jul 19, 2009 at 11:15:39AM +0100, Pedro Doria Meunier wrote:
> > I have a table ~18M rows with a 'timestamp with time zone' column. It's
> > indexed thus:
> >
> > CREATE INDEX my_table_timestamp_idx
> >   ON my_table
> >   USING btree
> >   (zulu_timestamp);
>
> Based on your query, I think you want a multi-column index---probably on
> (id,zulu_timestamp).
>
> The problem with just having an index on either column is that it's
> difficult to combine them and PG hence just thinks that it will be

Since 8.1 PG can do an bitmap index scan using both indexes...

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Timestamp indicies not being used!

From
Sam Mason
Date:
On Sun, Jul 19, 2009 at 02:56:04PM +0200, Andreas Kretschmer wrote:
> Sam Mason <sam@samason.me.uk> wrote:
> > The problem with just having an index on either column is that it's
> > difficult to combine them and PG hence just thinks that it will be
>
> Since 8.1 PG can do an bitmap index scan using both indexes...

Yes, and with decent stats (as you pointed out) it'll know better when
to do this.  Even with better stats it may not help much as there could
be cross-column correlations that are causing PG's estimates to be off.

--
  Sam  http://samason.me.uk/

Re: Timestamp indicies not being used!

From
Tom Lane
Date:
Andreas Kretschmer <akretschmer@spamfence.net> writes:
> Sam Mason <sam@samason.me.uk> wrote:
>> Based on your query, I think you want a multi-column index---probably on
>> (id,zulu_timestamp).
>>
>> The problem with just having an index on either column is that it's
>> difficult to combine them and PG hence just thinks that it will be

> Since 8.1 PG can do an bitmap index scan using both indexes...

... which produces an unordered result, so it's pretty useless for this
problem.  I concur with Sam's suggestion.

            regards, tom lane

[SOLVED] Re: Timestamp indicies not being used!

From
Pedro Doria Meunier
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

First of all *thank* you very much to all that replied! :)

Sam's suggestion actually did the trick!

I created the multi-column index and the stalling went away. Yay!

Best regards,

Pedro Doria Meunier
GSM: +351 96 17 20 188
Skype: pdoriam




Tom Lane wrote:
> Andreas Kretschmer <akretschmer@spamfence.net> writes:
>> Sam Mason <sam@samason.me.uk> wrote:
>>> Based on your query, I think you want a multi-column
>>> index---probably on (id,zulu_timestamp).
>>>
>>> The problem with just having an index on either column is that
>>> it's difficult to combine them and PG hence just thinks that it
>>> will be
>
>> Since 8.1 PG can do an bitmap index scan using both indexes...
>
> ... which produces an unordered result, so it's pretty useless for
> this problem.  I concur with Sam's suggestion.
>
> regards, tom lane
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org

iD8DBQFKY2hd2FH5GXCfxAsRAjj7AJ4lGNmNuwPUuTv6EwZ8s2faZcOASACgka8P
aE4vwzcy3cIdbEyFtYWTjAw=
=I5Jv
-----END PGP SIGNATURE-----