2

Consider the following table

CREATE TABLE COMPANY(
   ID BIGINT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

If we have 100 million random data in this table.

Select age from company where id=2855265

Executed in less than a millisecond

Select age from company where id<353

Return less than 50 rows and Executed in less than a millisecond

Both query uses index

But the following query use full table scan and executed in 3 seconds

Select age from company where id<2855265

Return less than 500 rows

How can I speed up the query that select primary key less than variable?

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
user7375496
  • 21
  • 1
  • Have you `ANALYZE`d the table? If so, what is your current `statistics_target`? What does the `EXPLAIN ANALYZE` output look like for the three different queries? – jmelesky Jan 04 '17 at 17:59
  • 1
    Please read: https://stackoverflow.com/tags/postgresql-performance/info then **[edit]** your question and add the missing information. –  Jan 04 '17 at 18:52

2 Answers2

1

Performance

The predicate id < 2855265 potentially returns a large percentage of rows in the table. Unless Postgres has information in table statistics to expect only around 500 rows, it might switch from an index scan to a bitmap index scan or even a sequential scan. Explanation:

We would need to see the output from EXPLAIN (ANALYZE, BUFFERS) for your queries.

When you repeat the query, do you get the same performance? There may be caching effects.

Either way, 3 seconds is way to slow for 500 rows, Postgres might be working with outdated or inexact table statistics. Or there may be issues with your server configuration (not enough resources). Or there can be several other not so common reasons, including hardware issues ...

If VACUUM ANALYZE did not help, VACUUM FULL ANALYZE might. It effectively rewrites the whole table and all indexes in pristine condition. Takes an exclusive lock on the table and might conflict with concurrent access!

I would also consider increasing the statistics target for the id column. Instructions:

Table definition?

Whatever else you do, there seem to be various problems with your table definition:

CREATE TABLE COMPANY(
   ID BIGINT PRIMARY KEY NOT NULL,  -- int is probably enough. "id" is a terrible column name
   NAME      TEXT    NOT NULL,  -- "name" is a teriible column name
   AGE       INT     NOT NULL,  -- typically bad idea to store age, store birthday instead
   ADDRESS   CHAR(50),   -- never use char(n)!
   SALARY    REAL        -- why would a company have a salary? never store money as real
);

You probably want something like this instead:

CREATE TABLE emmployee(
   emploee_id    serial PRIMARY KEY
   company_id    int NOT NULL  -- REFERENCES company(company_id)?
 , birthday      date NOT NULL
 , employee_name text NOT NULL
 , address       varchar(50)   -- or just text
 , salary        int           -- store amount as *Cents*
);

Related:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 1
    I think some of the advice on column naming is off, here. "ID" is a standard column name for integer primary keys when create by Ruby on Rails, and makes complete sense when just about every table has one, and I'd disagree with automatically prefixing column names with table names, like "employee_name". Why "employee_name" and "employee_id", and not "employee_address" or "employee_company_id"? – David Aldridge Jan 04 '17 at 23:12
  • @DavidAldridge: "employee_name" is a guess because I don't know what's in the column - "name" didn't tell me much about it. I'd only advocate an automatic prefix for surrogate PKs - "emploee_id" in this case. "name" and "id" are common, non-descriptive anti-patterns for column names. ([See my rationale in the linked answer.](http://stackoverflow.com/a/9790225/939860)) Some ORMs use "id" as column name, that's an unfortunate decision on their side. But you are right, the bits about naming convention are my opinion rather than an universal truth. A widely held opinion among DBAs I might add. – Erwin Brandstetter Jan 04 '17 at 23:29
0

You will need to perform a VACUUM ANALYZE company; to update the planning.

jlee88my
  • 2,935
  • 21
  • 28