hi,

=0A

I have a problem s= pace where the main goal is to search backward in time for events. Ti= me can go back very far into the past, and so the

=0A

table can get= quite large. However, the vast majority of queries are all satisfied= by relatively recent data. I have an index on the row creation date = and I would like almost all of my queries to have a query plan looking some= thing like:

=0A

Limit ...

= -> Index= Scan Backward using server_timestamp_idx on events (cost=3D0.= 00..623055.91 rows=3D8695 width=3D177)

=0A

&nb= sp; ...

However, PostgreSQL frequently= tries to do a full table scan. Often what controls whether a scan is= performed or not is dependent on the size of the LIMIT and how detailed th= e WHERE clause is. In practice, the scan is always the wrong answer f= or my use cases (where "always" is defined to be >99.9%).

= =0A

=0A

(1) A sample query that devolves to a full = table scan

=0A

EXPLAIN

SELECT events.id, events.client_duration, events.message, events.= created_by, events.source, events.type, events.event, events.environment,

=0A

events.server_timestamp,= events.session_id, events.reference, events.client_uuid

<= font class=3D"Apple-style-span" face=3D"'courier new', monospace"> &nb= sp; FROM events

=0A

WHERE client_uuid ~* E'^foo bar so what'

ORDER BY server_timestamp DESC

=0A

&= nbsp;LIMIT 20;

&nb= sp; QUERY PLA= N (BAD!)

=0A

---------------= -----------------------------------------------------------

= Limit (cost=3D363278.56..363278.61 rows=3D20 width=3D177)=0A

-> Sort (cost=3D363278.56..363278.62 rows=3D24= width=3D177)

Sort Key: server_t= imestamp

=0A

-> Seq Scan on events = (cost=3D0.00..363278.01 rows=3D24 width=3D177)

=0A &nb= sp; Filter: (client_uuid ~* '^foo bar so= what'::text)

=0A(2) Making the query faster by making the string match LESS specif= ic (odd, seems like it should be MORE)

=0A

EXPLAIN

SELECT events.id, events.client_d= uration, events.message, events.created_by, events.source, events.type, eve= nts.event, events.environment,

=0A

= ; events.server_timestamp, events.session_id, events.reference, event= s.client_uuid

FROM events

=0A

= &n= bsp; WHERE client_= uuid ~* E'^foo'

ORDER BY server_timestamp DESC=

=0A

LIMIT 20;

&= nbsp; = QUERY PLAN (GOOD!) &nb= sp; &= nbsp;

=0A

-------------------------------------------= -----------------------------------------------------------------

Limit (cost=3D0.00..1433.14 rows=3D20 width=3D177)

&= nbsp; -> = Index Scan Backward using server_timestamp_idx on events (cost= =3D0.00..623055.91 rows=3D8695 width=3D177)

=0A

= ; Filter: (client_uuid ~* '^foo'::text)

<= div>

(3) Alte= rnatively making the query faster by using a smaller limit=0A

EXPLAIN
=0A

SELECT events.id, event= s.client_duration, events.message, events.created_by, events.source, events= .type, events.event, events.environment,

=0A

&n= bsp; events.server_timestamp, events.session_id, events.refere= nce, events.client_uuid

FROM events=0A

ORDER BY server_timestamp DESC

=0A

LIMIT = 10;

<= /div>

(GOOD!) = = ;

------------= ---------------------------------------------------------------------------= -------------------

=0A

Limit (cost=3D0.00..259606.63 = rows=3D10 width=3D177)

-> Index Scan Backward using server_t= imestamp_idx on events (cost=3D0.00..623055.91 rows=3D24 width=3D177)=

=0A

Filter: (client_uuid ~* '^f= oo bar so what'::text)

I find mysel= f wishing I could just put a SQL HINT on the query to force the index to be= used but I understand that HINTs are considered harmful and are therefore = not provided for PostgreSQL, so what is the recommended way to solve this?<= /div>=0A

thank you very much

=0A