Databases
StackOverflow2013
Postsdbo
POPostgreSQL query doesn't use index
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPostgreSQL query doesn't use index
    primarykey
    data
    text
    <p>I have a very simple db schema, which has a multi column b-tree index on following columns: </p> <pre><code>PersonId, Amount, Commission </code></pre> <p>Now, if I try to select the table with following query:</p> <pre><code>explain select * from "Order" where "PersonId" = 2 AND "Commission" &gt; 3 </code></pre> <p>Pg is scanning the index and the query is very fast, but if I try the following query:</p> <pre><code>explain select * from "Order" where "PersonId" &gt; 2 AND "Commission" &gt; 3 </code></pre> <p>It does a sequential scan, even when the index is present. Even this query</p> <pre><code>explain select * from "Order" where "Commission" &gt; 3 </code></pre> <p>does a sequential scan. Anyone care to explain why? :-)</p> <p>Thank you very much.</p> <p><strong>UPDATE</strong></p> <p>The table contains 100 million rows. I have created it just to test PostgreSQL performance against MS SQL. The table is already VACUUMED. I'm runnning Core I5 2500k quad core cpu and 8 GB of ram.</p> <p>Here's the result of explain analyze for this query:</p> <pre><code>explain ANALYZE select * from "Order" where "Commission" BETWEEN 3000000 AND 3000010 LIMIT 20 Limit (cost=0.00..2218328.00 rows=1 width=24) (actual time=28043.249..28043.249 rows=0 loops=1) -&gt; Seq Scan on "Order" (cost=0.00..2218328.00 rows=1 width=24) (actual time=28043.247..28043.247 rows=0 loops=1) Filter: (("Commission" &gt;= 3000000::numeric) AND ("Commission" &lt;= 3000010::numeric)) Total runtime: 28043.278 ms </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDavita
    1. USDavita
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COlook there http://stackoverflow.com/questions/5203755/why-does-postgresql-perform-sequential-scan-on-indexed-column, especially @Frank Heikens answer.
      singulars
      1. POPostgreSQL query doesn't use index
      1. USRaphaël Althaus
    2. CObecause the index column order matters. read this: http://use-the-index-luke.com/sql/where-clause/searching-for-ranges/greater-less-between-tuning-sql-access-filter-predicates
      singulars
      1. POPostgreSQL query doesn't use index
      1. USMarkus Winand
    3. COCan you show us the `CREATE TABLE` and `CREATE INDEX` statements for the table? The statements you used to populate the table would be fantastic to have, too, if practicable. (`\d "Order"` output from psql would *do*, but it makes it harder for people to duplicate your results and test suggested changes.) I'm assuming the data is fully cached, because otherwise it would be awfully hard to pass 100 million rows in 28 seconds. The default configuration assumes minimal caching, so a plan based on the assumption of disk access will be chosen, which weights against random index access.
      singulars
      1. POPostgreSQL query doesn't use index
      1. USkgrittn
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload