agora inbox for [email protected]
help / color / mirror / Atom feedFrom: Constantin Teodorescu <[email protected]>
To: [email protected] <[email protected]>
Subject: Interesting behaviour !
Date: Wed, 14 Jul 1999 07:16:28 +0000
Message-ID: <[email protected]> (raw)
Please Cc: to [email protected]
I have a table called "note" that looks like this :
create table "note" (id serial,perioada int2,schema int2,explicatie
text,...);
The "note" table has 22.000 records and the record length is about 75
bytes (is has also a "text" field").
Because I am frequently accesing the table with queries like "... where
perioada=12" I was tempted to make also indexes on "perioada" and
"schema" field.
The tables have the following sizes (their file sizes into
/usr/local/pgsql/data/base....)
note 2.890 Kb
note_id 385 Kb
note_perioada 409 Kb
note_schema 466 Kb
I ran previusly "vacuum analyze" on that database ensuring that
statistical tables have been updated.
Trying some selects with explain I got the following results:
contabil=> explain select * from note where id=15;
NOTICE: QUERY PLAN:
Index Scan using note_id on note (cost=2.05 rows=2 width=87)
contabil=> explain select * from note where perioada=15;
NOTICE: QUERY PLAN:
Seq Scan on note (cost=1099.99 rows=1600 width=87)
contabil=> explain select * from note where schema=15;
NOTICE: QUERY PLAN:
Seq Scan on note (cost=1099.99 rows=432 width=87)
That means that searching on "perioada" field don't use "note_perioada"
index!!!
I know that the query optimisation take care of record lengths, table
sizes, index sizes, but I thought that in this case it will use
"note_perioada" index.
The distribution of "perioada" values within "note" records is like that
:
contabil=> select perioada,count(*) from note group by perioada;
perioada|count
--------+-----
4| 2
7| 66
8| 108
9| 135
10| 151
11| 146
12| 4468
13| 3045
14| 3377
15| 3207
16| 3100
17| 3039
18| 1789
19| 1
22| 2
(15 rows)
So, I think that PostgreSQL is doing right when he chooses not to use
"note_perioada" index for that type of query by comparing different
costs (althought it still remains strange at the first look).
Is there any chance to speed up that type of simple query (select * from
note where perioada=N) ?
I dropped the index and try with a "hash" index on the same "perioada"
field. The same result.
In this case, it seems that the "note_perioada" index will never be
used. That means it can be safely dropped without affecting the
application performance, isn't it? It is expected that the database will
grow in the same manner, with approx. the same nr. of records per
"perioada" field every month.
Best regards,
Please Cc: to [email protected]
===============================
Constantin Teodorescu
FLEX Consulting Braila, ROMANIA
view thread (16+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: Interesting behaviour !
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox