Thread: Antw: Re: Query questions
My version is 8.1.4. Here is the plan for the query, it's performed on a
smaller table, though because i can't access the biggest table at the
moment:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on mytable (cost=67.48..16738.24 rows=6519 width=10)
(actual time=34.033..1903.106 rows=1 loops=1)
Recheck Cond: ((pid)::text = 'ZZZ000110011'::text)
Filter: ((crit)::text = '915677'::text)
-> Bitmap Index Scan on idx_pid_22 (cost=0.00..67.48 rows=8996
width=0) (actual time=12.998..12.998 rows=6207 loops=1)
Index Cond: ((pid)::text = 'ZZZ000110011'::text)
Total runtime: 1903.894 ms
And yes i have indexes on both pid (varchar(15)) and crit(varchar(13)).
>>> "A. Kretschmer" <andreas.kretschmer@schollglas.com> 31.07.06 15.49
Uhr >>>
am 31.07.2006, um 15:32:19 +0200 mailte Christian Rengstl folgendes:
> Hi list,
>
> i have a problem with creating a query and i hope somebody can give me
> some hints. I have the following table
> pid(varchar), crit(varchar), val1(varchar), val2(varchar),
> iDate(timestamp)
> where there are up to 63 million lines with 1500 distinct pids and
> around 42000 distinct crits:
> pid crit val1 val2 iDate
> 'yyy' 'aaa' 'b' 'c' someTime
> 'yyy' 'bbb' 'b' 'a' anotherTime
> ...
> What i have to do is to export the table for which i have to query the
> table with the following pattern: select val1, val2 from mytable where
> pid='yyy' and crit='aaa'. But if i do this 63 million times, it just
> takes too long. So, what i would like to do is to make a query where i
Do you have indexe on pid and crit?
Can you paste a "explain analyse select val1, val2 from mytable where
pid='yyy' and crit='aaa';"
Which version? ("select version();").
HTH, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
Christian Rengstl wrote: > My version is 8.1.4. Here is the plan for the query, it's performed on a > smaller table, though because i can't access the biggest table at the > moment: > QUERY PLAN > > ----------------------------------------------------------------------------------------------------------------------------- > > Bitmap Heap Scan on mytable (cost=67.48..16738.24 rows=6519 width=10) > (actual time=34.033..1903.106 rows=1 loops=1) > Recheck Cond: ((pid)::text = 'ZZZ000110011'::text) > > Filter: ((crit)::text = '915677'::text) > > -> Bitmap Index Scan on idx_pid_22 (cost=0.00..67.48 rows=8996 > width=0) (actual time=12.998..12.998 rows=6207 loops=1) > Index Cond: ((pid)::text = 'ZZZ000110011'::text) > > Total runtime: 1903.894 ms > > And yes i have indexes on both pid (varchar(15)) and crit(varchar(13)). That's not quite what Andreas asked. Do you have an index on (pid, crit)? -- Richard Huxton Archonet Ltd
am 31.07.2006, um 15:55:39 +0100 mailte Richard Huxton folgendes: > Christian Rengstl wrote: > >My version is 8.1.4. Here is the plan for the query, it's performed on a > > Total runtime: 1903.894 ms > >And yes i have indexes on both pid (varchar(15)) and crit(varchar(13)). > > That's not quite what Andreas asked. Do you have an index on (pid, crit)? He has 8.1, and i think, we have bitmap index scan for such tasks... (we don't need a index over both columns) Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
A. Kretschmer wrote: > am 31.07.2006, um 15:55:39 +0100 mailte Richard Huxton folgendes: >> Christian Rengstl wrote: >>> My version is 8.1.4. Here is the plan for the query, it's performed on a >>> Total runtime: 1903.894 ms >>> And yes i have indexes on both pid (varchar(15)) and crit(varchar(13)). >> That's not quite what Andreas asked. Do you have an index on (pid, crit)? > > He has 8.1, and i think, we have bitmap index scan for such tasks... > (we don't need a index over both columns) Hmm - I'd have assumed an index spanning both columns would be a win where the selectivity was reasonable (as it looked here). Reading through the original post, I'd be tempted to see if an index on (pid,crit,iDate) could be used for the sorting too. If the planner is smart enough to spot it, that would presumably be the best result. -- Richard Huxton Archonet Ltd