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_f ome"
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_pke y" primary key, btree (fomeazon)
"idx_t_fome_fom eazon" btree (fomeazon)
"idx_t_fome_inu se" btree (inuse)
"idx_t_fome_low er_inuse" btree (lower((inuse): :text))
"idx_t_fome_mec sazon" btree (mecsazon)
tgr=# \d t_me30 -- 4518927 rows
Table "public.t_m e30"
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_ert ektipus" btree (ertektipus)
"idx_t_me30_fom eazon" btree (fomeazon)
"idx_t_me30_mer tido" btree (mertido)
"idx_t_me30_mer tido_fomeazon_e rtektipus" btree (mertido, fomeazon,
ertektipus)
"idx_t_me30_utm odido" 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.8 0 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.ertektip us
-> Hash IN Join (cost=1.81..128 045.32 rows=24 width=46)
Hash Cond: ("outer".fomeaz on = "inner".fomeazo n)
-> Seq Scan on t_me30 (cost=0.00..128 037.62 rows=1129 width=46)
Filter: ((mertido ~~ '2003-12-17%'::text) AND (ertektipus =
'+MW'::bpchar))
-> Hash (cost=1.80..1.8 0 rows=1 width=4)
-> Seq Scan on t_fome (cost=0.00..1.8 0 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****** ***********@pos tgresql.org]On Behalf Of Tom Lane
Sent: 2004. január 6. 21:04
To: cs*****@vnet.hu
Cc: Pg***********@P ostgresql.Org (E-mail)
Subject: Re: [GENERAL] Query performance question on a large table
=?iso-8859-2?Q?Egy=FCd_Csa ba?= <cs*****@vnet.h u> 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*******@postg resql.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