Hi Tom,
I've upgraded to 7.4.1. It seems to be working fine - haven't encountered
any problems yet.
The upgrade didn't lead to the desired outcome however. The query doesn't
run faster then under v7.3.2.
I have the following relations:
tgr=# \d t_fome -- 46 rows
Table "public.t_fome"
Column | Type | Modifiers
-----------+--------------------------+-----------
fomeazon | integer | not null
fomenev | character varying(50) |
inuse | character(4) |
mecsazon | integer |
merotipus | character(10) |
szbevont | character(1) |
utmodido | timestamp with time zone |
visible | character(1) |
Indexes:
"t_fome_pkey" primary key, btree (fomeazon)
"idx_t_fome_fomeazon" btree (fomeazon)
"idx_t_fome_inuse" btree (inuse)
"idx_t_fome_lower_inuse" btree (lower((inuse)::text))
"idx_t_fome_mecsazon" btree (mecsazon)
tgr=# \d t_me30 -- 4518927 rows
Table "public.t_me30"
Column | Type | Modifiers
--------------+--------------------------+-----------
fomeazon | integer |
mertido | character(16) |
ertektipus | character(10) |
hetnap | character(1) |
impulzusszam | double precision |
mertertek | double precision |
merttartam | integer |
utmodido | timestamp with time zone |
Indexes:
"idx_t_me30_ertektipus" btree (ertektipus)
"idx_t_me30_fomeazon" btree (fomeazon)
"idx_t_me30_mertido" btree (mertido)
"idx_t_me30_mertido_fomeazon_ertektipus" btree (mertido, fomeazon,
ertektipus)
"idx_t_me30_utmodido" btree (utmodido)
I found that:
1. explain select fomeazon from t_fome where lower(inuse) = 'igen'
QUERY PLAN
------------------------------------------------------
Seq Scan on t_fome (cost=0.00..1.80 rows=1 width=4)
Filter: (lower((inuse)::text) = 'igen'::text)
As the table has an index on lower((inuse)::text), I belive it should be
used for searching.
2. explain select mertido, fomeazon, ertektipus, mertertek
from t_me30 where fomeazon in (select fomeazon from t_fome where
lower(inuse) = 'igen') and
mertido like '2003-12-17%' and ertektipus in ('+MW') order by mertido,
fomeazon, ertektipus;
QUERY PLAN
----------------------------------------------------------------------------
---------------
Sort (cost=128045.87..128045.93 rows=24 width=46)
Sort Key: t_me30.mertido, t_me30.fomeazon, t_me30.ertektipus
-> Hash IN Join (cost=1.81..128045.32 rows=24 width=46)
Hash Cond: ("outer".fomeazon = "inner".fomeazon)
-> Seq Scan on t_me30 (cost=0.00..128037.62 rows=1129 width=46)
Filter: ((mertido ~~ '2003-12-17%'::text) AND (ertektipus =
'+MW'::bpchar))
-> Hash (cost=1.80..1.80 rows=1 width=4)
-> Seq Scan on t_fome (cost=0.00..1.80 rows=1 width=4)
Filter: (lower((inuse)::text) = 'igen'::text)
In the first line of query plan we have a sort operation which is the most
expensive part of the plan. Having an index on (mertido, fomeazon,
ertektipus) key, shouldn't it be used to sort the result set? Like doesn't
use the index (mertido) either.
How could I make Postgres to use these indexes. Is there any other way to
make lower the costs on sort operations and as a result the query run time?
Thank you all,
-- Csaba Együd
-----Original Message-----
From: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org]On Behalf Of Tom Lane
Sent: 2004. január 6. 21:04
To: cs*****@vnet.hu
Cc: Pg***********@Postgresql.Org (E-mail)
Subject: Re: [GENERAL] Query performance question on a large table
=?iso-8859-2?Q?Egy=FCd_Csaba?= <cs*****@vnet.hu> writes: here is a sample query:
select mertido, fomeazon, ertektipus, mertertek from
t_me30 where fomeazon in (select distinct fomeazon from t_fome where lower(inuse)
= 'igen') and mertido like '2003-12-17%' and ertektipus in ('+MW') order
by mertido, fomeazon, ertektipus;
Ohh, I nearly forgot the config: Linux 7.1; Postgres 7.3.2;
The first thing you ought to do is move to PG 7.4. "foo IN
(SELECT ...)"
generally works a lot better under 7.4 than prior releases.
I'd suggest
dropping the "DISTINCT" when using 7.4, too.
regards, tom lane
---------------------------(end of
broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to
ma*******@postgresql.org
-- Incoming mail is certified Virus Free.
Checked by AVG Anti-Virus (http://www.grisoft.com).
Version: 7.0.209 / Virus Database: 261 - Release Date: 2004. 01. 02.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend