Optimizing Postgres Query

[Anon]

In trying to explain and optimize all my queries, I've come across something hopefully someone can help me understand.

The following query:

explain SELECT current_stats_logged, current_stats_new_members, current_stats_chat FROM db_current_stats;
NOTICE: QUERY PLAN:

Seq Scan on db_current_stats (cost=100000000.00..100000001.00 rows=1 width=24)

now there will ALWAYS only be one row in this table.....I am wondering just how come the explained cost is SO HIGH...

anyone ?? Thanks

• Keith

[toma42]

try the same explain against another table and see if the cost is as high? Explain says how it will query. The cost is probably high because it expects it will return a lot of rows....or, perhaps you don't have a primary key (unlikely).

[[deleted]]

As Tom said, explain prints how the query-optimizer sizes your query up.

How expensive the optimizer estimates your query has nothing at all to do with how many rows there are in the table (it doesn't know that yet). A sequencial scan is always the most expensive thing it can do, so it must scale that in pretty high.

And yes, the fact that the query optimizer doesn know the seize of the tables does have it's drawbacks, because on very small tables the optimizer could decide that using an index will be less expensive even if it actually takes longer to read the index than to do a sequencial scan.

[smarlowe]

Run 'vacuum analyze' against the database first and see what explain says then.