Sfrgttk

Travelling in US for more than 90 days

Are hand made posters acceptable in Academia?

"Oh no!" in Latin

Capacitor electron flow

Is divisi notation needed for brass or woodwind in an orchestra?

Showing mass murder in a kid's book

How do I lift the insulation blower into the attic?

Can a Knock spell open the door to Mordenkainen's Magnificent Mansion?

Is there a POSIX way to shutdown a UNIX machine?

Air travel with refrigerated insulin

What is the period/term used describe Giuseppe Arcimboldo's style of painting?

How to split IPA spelling into syllables

Should I warn a new PhD Student?

Turning a hard to access nut?

Friend wants my recommendation but I don't want to give it to him

How to test the sharpness of a knife?

Make a Bowl of Alphabet Soup

Why does a 97 / 92 key piano exist by Bosendorfer?

1 John in Luther’s Bibel

Why do Radio Buttons not fill the entire outer circle?

Is this saw blade faulty?

Offset in split text content

A seasonal riddle

Do people actually use the word "kaputt" in conversation?

Why doesn't tsrange('2018-01-01', NULL) @> transactions.transacted_at use the b-tree index?

PostgreSQL: Slow Update QueryNested aggregate functions over different dimensionsPostgres SQL - Compare Count of rows, returning where a < b or b doesn't existChange an index order from ascending to descendingHow to find out what operator class and access method should be used for an exclusion constraint?Optimize a trigram search with custom sort orderOptimizing a “categorized” search table using trigramsHow to access to other INSERTed/UPDATEd rows from trigger in PostgreSQL?Postgresql is failing to use my covering index and falling back to a much slower bitmap scanPostgreSQL most efficient way to reference multiple tables

0

Given

CREATE TABLE transactions

(

    transacted_at TIMESTAMP

);



CREATE INDEX IF NOT EXISTS index_transactions_on_transacted_at

    ON transactions (transacted_at);

Then this doesn't use the index:

SELECT * FROM transactions

WHERE tsrange('2018-01-01', NULL) @> transacted_at;

But this does:

SELECT * FROM transactions

WHERE transacted_at > '2018-02-01'

I realize that I can simply deconstruct the range and do the comparison that works, but I'd like to know how I can use ranges natively with an index on the transactions table.

What am I missing?

postgresql-9.6

share

asked 2 mins ago

Kit SundeKit Sunde

139110

add a comment | 

0

Given

CREATE TABLE transactions

(

    transacted_at TIMESTAMP

);



CREATE INDEX IF NOT EXISTS index_transactions_on_transacted_at

    ON transactions (transacted_at);

Then this doesn't use the index:

SELECT * FROM transactions

WHERE tsrange('2018-01-01', NULL) @> transacted_at;

But this does:

SELECT * FROM transactions

WHERE transacted_at > '2018-02-01'

I realize that I can simply deconstruct the range and do the comparison that works, but I'd like to know how I can use ranges natively with an index on the transactions table.

What am I missing?

postgresql-9.6

share

asked 2 mins ago

Kit SundeKit Sunde

139110

add a comment | 

Given

CREATE TABLE transactions

(

    transacted_at TIMESTAMP

);



CREATE INDEX IF NOT EXISTS index_transactions_on_transacted_at

    ON transactions (transacted_at);

Then this doesn't use the index:

SELECT * FROM transactions

WHERE tsrange('2018-01-01', NULL) @> transacted_at;

But this does:

SELECT * FROM transactions

WHERE transacted_at > '2018-02-01'

I realize that I can simply deconstruct the range and do the comparison that works, but I'd like to know how I can use ranges natively with an index on the transactions table.

What am I missing?

postgresql-9.6

share

asked 2 mins ago

Kit SundeKit Sunde

139110

CREATE TABLE transactions

(

    transacted_at TIMESTAMP

);



CREATE INDEX IF NOT EXISTS index_transactions_on_transacted_at

    ON transactions (transacted_at);

SELECT * FROM transactions

WHERE tsrange('2018-01-01', NULL) @> transacted_at;

SELECT * FROM transactions

WHERE transacted_at > '2018-02-01'

share

asked 2 mins ago

Kit SundeKit Sunde

139110

share

asked 2 mins ago

Kit SundeKit Sunde

139110

asked 2 mins ago

Kit SundeKit Sunde

139110

asked 2 mins ago

Kit SundeKit Sunde

139110