Re: Query performance question on a large table - Mailing list pgsql-general

Sorry, I just have found the thread concerning about index useage in
connection with like clause.
I created an index on mertido using
CREATE INDEX idx_t_me30_mertido2 ON t_me30 USING btree (mertido
bpchar_pattern_ops);
command and analyzed and the query plan now is:

tgr=# 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 i
n ('+MW') order by mertido, fomeazon, ertektipus;
                                               QUERY PLAN
----------------------------------------------------------------------------
----------------------------
 Sort  (cost=7.84..7.85 rows=1 width=46)
   Sort Key: t_me30.mertido, t_me30.fomeazon, t_me30.ertektipus
   ->  Nested Loop IN Join  (cost=0.00..7.83 rows=1 width=46)
         Join Filter: ("outer".fomeazon = "inner".fomeazon)
         ->  Index Scan using idx_t_me30_mertido2 on t_me30
(cost=0.00..6.02 rows=1 width=46)
               Index Cond: ((mertido ~>=~ '2003-12-17'::bpchar) AND (mertido
~<~ '2003-12-18'::bpchar))
               Filter: ((mertido ~~ '2003-12-17%'::text) AND (ertektipus =
'+MW'::bpchar))
         ->  Seq Scan on t_fome  (cost=0.00..1.80 rows=1 width=4)
               Filter: (lower((inuse)::text) = 'igen'::text)

Thats it! Thank you very much!

-- Csaba

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Együd Csaba
> Sent: 2004. január 7. 20:17
> To: 'Tom Lane'
> Cc: 'Pgsql-General@Postgresql.Org (E-mail)'
> Subject: Re: [GENERAL] Query performance question on a large table
>
>
> 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: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Tom Lane
> > Sent: 2004. január 6. 21:04
> > To: csegyud@vnet.hu
> > Cc: Pgsql-General@Postgresql.Org (E-mail)
> > Subject: Re: [GENERAL] Query performance question on a large table
> >
> >
> > =?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud@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
> > majordomo@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
>
>
>
> -- 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.
>