Thread: SQL query help!
Hello! I hope that someone here could help. I'm using PostgreSQL7.1.3 I have 3 tables in my DB: the tables are defined in the following way: CREATE TABLE category( id SERIAL NOT NULL PRIMARY KEY, // etc etc ) ; CREATE TABLE subcategory( id SERIAL NOT NULL PRIMARY KEY, categoryid int CONSTRAINT subcategory__ref_categoryREFERENCES category (id)// etc etc ) ; CREATE TABLE entry( entryid SERIAL NOT NULL PRIMARY KEY, isapproved CHAR(1) NOT NULL DEFAULT 'n', subcategoryid int CONSTRAINT entry__ref_subcategoryREFERENCES subcategory (id)// atd , ) ; I have the following SQL query : "SELECT * FROM entry where isapproved='y' AND subcategoryid IN (SELECT id FROM subcategory WHERE categoryid='"+catID+"') ORDER BY subcategoryid DESC"; For a given categoryid( catID), the query will return all entries in the "entry" table having a corresponding subcategoryid(s)[returned by the inned subquery]. But I want to return only a limited number of entries of each subcategory..... let's say that I want to return at most 5 entries of each subcategory type ( for instance if the inner subquery returns 3 results, thus I will be having in total at most 15 entries as relust).... How can this be achieved? I'm aware of postgreSQL "LIMIT" and "GROUP BY" clause..... but so far, I'm not able to put all this together... Thanks in advance. Arcadius.
Tell me what did you try with limit and group by. Where's IN, why don't you use EXISTS instead. It runs much master ! Regards, Luis Sousa Arcadius A. wrote: >Hello! > >I hope that someone here could help. > >I'm using PostgreSQL7.1.3 > >I have 3 tables in my DB: the tables are defined in the following way: > > >CREATE TABLE category( >id SERIAL NOT NULL PRIMARY KEY, >// etc etc > >) >; > >CREATE TABLE subcategory( >id SERIAL NOT NULL PRIMARY KEY, >categoryid int CONSTRAINT subcategory__ref_category > REFERENCES category (id) > // etc etc >) >; > >CREATE TABLE entry( >entryid SERIAL NOT NULL PRIMARY KEY, >isapproved CHAR(1) NOT NULL DEFAULT 'n', >subcategoryid int CONSTRAINT entry__ref_subcategory > REFERENCES subcategory (id) > // atd >, >) >; > > >I have the following SQL query : > > "SELECT * FROM entry where isapproved='y' AND subcategoryid IN (SELECT id >FROM subcategory WHERE >categoryid='"+catID+"') ORDER BY subcategoryid DESC"; > > >For a given categoryid( catID), the query will return all entries in the >"entry" table >having a corresponding subcategoryid(s)[returned by the inned subquery]. > >But I want to return only a limited number of entries of each >subcategory..... let's say that I want to return at most 5 entries of each >subcategory type ( for instance if the inner subquery returns 3 results, >thus I will be having in total at most 15 entries as relust).... > >How can this be achieved? > >I'm aware of postgreSQL "LIMIT" and "GROUP BY" clause..... but so far, I'm >not able to put all this together... > >Thanks in advance. > >Arcadius. > > > > > > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster > > > >
Hi,
i run 2 queries on 2 similar boxes (one running Linux 2.4.7, redhat 7.1
and the other running FreeBSD 4.7-RELEASE-p2)
The 2 boxes run postgresql 7.2.3.
I get some performance results that are not obvious (at least to me)
i have one table named "noon" with 108095 rows.
The 2 queries are:
q1: SELECT count(*) from noon;
q2: SELECT * from noon;
Linux q1
========
dynacom=# EXPLAIN ANALYZE SELECT count(*) from noon;
NOTICE: QUERY PLAN:
Aggregate (cost=20508.19..20508.19 rows=1 width=0) (actual
time=338.17..338.17
rows=1 loops=1)
-> Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=0) (actual
time=0.01..225.73 rows=108095 loops=1)
Total runtime: 338.25 msec
Linux q2
========
dynacom=# EXPLAIN ANALYZE SELECT * from noon;
NOTICE: QUERY PLAN:
Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=1960) (actual
time=1.22..67909.31 rows=108095 loops=1)
Total runtime: 68005.96 msec
FreeBSD q1
==========
dynacom=# EXPLAIN ANALYZE SELECT count(*) from noon;
NOTICE: QUERY PLAN:
Aggregate (cost=20508.19..20508.19 rows=1 width=0) (actual
time=888.93..888.94
rows=1 loops=1)
-> Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=0) (actual
time=0.02..501.09 rows=108095 loops=1)
Total runtime: 889.06 msec
FreeBSD q2
==========
dynacom=# EXPLAIN ANALYZE SELECT * from noon;
NOTICE: QUERY PLAN:
Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=1975) (actual
time=1.08..53470.93 rows=108095 loops=1)
Total runtime: 53827.37 msec
The pgsql configuration for both systems is identical
(the FreeBSD system has less memory but vmstat dont show
any paging activity so i assume this is not an issue here).
The interesting part is that FreeBSD does better in select *,
whereas Linux seem to do much better in select count(*).
Paging and disk IO activity for both systems is near 0.
When i run the select count(*) in Linux i notice a small
increase (15%) in Context Switches per sec, whereas in FreeBSD
i notice a big increase in Context Switches (300%) and
a huge increase in system calls per second (from normally
9-10 to 110,000).
(Linux vmstat gives no syscall info).
The same results come out for every count(*) i try.
Is it just the reporting from explain analyze??
Has any hacker some light to shed??
Thanx.
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> Linux q1
> ========
> dynacom=# EXPLAIN ANALYZE SELECT count(*) from noon;
> NOTICE: QUERY PLAN:
> Aggregate (cost=20508.19..20508.19 rows=1 width=0) (actual
> time=338.17..338.17
> rows=1 loops=1)
> -> Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=0) (actual
> time=0.01..225.73 rows=108095 loops=1)
> Total runtime: 338.25 msec
> Linux q2
> ========
> dynacom=# EXPLAIN ANALYZE SELECT * from noon;
> NOTICE: QUERY PLAN:
> Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=1960) (actual
> time=1.22..67909.31 rows=108095 loops=1)
> Total runtime: 68005.96 msec
You didn't say what was *in* the table, exactly ... but I'm betting
there are a lot of toasted columns, and that the extra runtime
represents the time to fetch (and perhaps decompress) the TOAST entries.
regards, tom lane
On Wed, 27 Nov 2002, Tom Lane wrote:
> Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> > Linux q1
> > ========
> > dynacom=# EXPLAIN ANALYZE SELECT count(*) from noon;
> > NOTICE: QUERY PLAN:
>
> > Aggregate (cost=20508.19..20508.19 rows=1 width=0) (actual
> > time=338.17..338.17
> > rows=1 loops=1)
> > -> Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=0) (actual
> > time=0.01..225.73 rows=108095 loops=1)
> > Total runtime: 338.25 msec
>
> > Linux q2
> > ========
> > dynacom=# EXPLAIN ANALYZE SELECT * from noon;
> > NOTICE: QUERY PLAN:
>
> > Seq Scan on noon (cost=0.00..20237.95 rows=108095 width=1960) (actual
> > time=1.22..67909.31 rows=108095 loops=1)
> > Total runtime: 68005.96 msec
>
> You didn't say what was *in* the table, exactly ... but I'm betting
> there are a lot of toasted columns, and that the extra runtime
> represents the time to fetch (and perhaps decompress) the TOAST entries.
278 columns of various types.
namely,
Table "noon"
Column | Type | Modifiers
------------------------+------------------------+-----------
v_code | character varying(4) |
log_no | bigint |
report_date | date |
report_time | time without time zone |
voyage_no | integer |
charterer | character varying(12) |
port | character varying(24) |
duration | character varying(4) |
rotation | character varying(9) |
me_do_cons | double precision |
reason | character varying(12) |
ancorage_date | date |
ancorage_time | time without time zone |
exp_berth_date | date |
exp_berth_time | time without time zone |
berth_date | date |
berth_time | time without time zone |
exp_sail_date | date |
exp_sail_time | time without time zone |
draft_fw | double precision |
draft_aft | double precision |
etc_date | date |
etc_time | time without time zone |
completion_date | date |
completion_time | time without time zone |
load_quantity | double precision |
discharging_quantity | double precision |
delivery_date | date |
delivery_place | character varying(12) |
redelivery_date | date |
redelivery_time | time without time zone |
redelivery_place | character varying(12) |
rob_ifo | double precision |
rob_mdo | double precision |
log_ifo | double precision |
log_mdo | double precision |
rcv_ifo | double precision |
rcv_mdo | double precision |
rcv_me | double precision |
rcv_cyl | double precision |
rcv_gen | double precision |
rob_me | double precision |
rob_cyl | double precision |
rob_gen | double precision |
voyage_sub_no | integer |
voyage_activity | character varying(3) |
remarks | character varying(60) |
latitude | character varying(6) |
longitude | character varying(6) |
speed | double precision |
wind_direction | character varying(1) |
rpm | double precision |
fuelconsumption | double precision |
me_bearing_oil_presure | double precision |
me_bearing_amber | double precision |
ambere | character varying(8) |
remarks2 | character varying(12) |
steam_hours | double precision |
ifoconsboilerheat | double precision |
ae_mdo_consumption | double precision |
cyl_me_exh_temp01 | double precision |
cyl_me_exh_temp02 | double precision |
cyl_me_exh_temp03 | double precision |
cyl_me_exh_temp04 | double precision |
cyl_me_exh_temp05 | double precision |
cyl_me_exh_temp06 | double precision |
cyl_me_exh_temp07 | double precision |
cyl_me_exh_temp08 | double precision |
cyl_me_exh_temp09 | double precision |
cyl_me_exh_temp10 | double precision |
cyl_me_exh_temp11 | double precision |
cyl_me_exh_temp12 | double precision |
cyl_me_exh_temp13 | double precision |
cyl_me_exh_temp14 | double precision |
gen1_ae_exh_temp01 | double precision |
gen1_ae_exh_temp02 | double precision |
gen1_ae_exh_temp03 | double precision |
gen1_ae_exh_temp04 | double precision |
gen1_ae_exh_temp05 | double precision |
gen1_ae_exh_temp06 | double precision |
gen1_ae_exh_temp07 | double precision |
gen1_ae_exh_temp08 | double precision |
gen2_ae_exh_temp01 | double precision |
gen2_ae_exh_temp02 | double precision |
gen2_ae_exh_temp03 | double precision |
gen2_ae_exh_temp04 | double precision |
gen2_ae_exh_temp05 | double precision |
gen2_ae_exh_temp06 | double precision |
gen2_ae_exh_temp07 | double precision |
gen2_ae_exh_temp08 | double precision |
gen3_ae_exh_temp01 | double precision |
gen3_ae_exh_temp02 | double precision |
gen3_ae_exh_temp03 | double precision |
gen3_ae_exh_temp04 | double precision |
gen3_ae_exh_temp05 | double precision |
gen3_ae_exh_temp06 | double precision |
gen3_ae_exh_temp07 | double precision |
gen3_ae_exh_temp08 | double precision |
dont_know | character varying(14) |
voyage_confirmation | character varying(1) |
ldin | double precision |
dist_to_go | integer |
dom_fw_rob | double precision |
fw_produced | double precision |
fw_salinity | double precision |
fw_cons_dom | double precision |
fw_cons_boil | double precision |
ifo_ballast | double precision |
ifo_deballast | double precision |
ifo_load | double precision |
ifo_disc | double precision |
ifo_blr_heat | double precision |
foofield | double precision |
sc_air_pr | double precision |
sc_air_temp | integer |
ae_oil_pr1 | double precision |
ae_oil_pr2 | double precision |
ae_oil_pr3 | double precision |
ae_oil_pr4 | double precision |
ae_oil_pr5 | double precision |
gen1_ex_9 | integer |
gen1_ex_10 | integer |
gen1_ex_11 | integer |
gen1_ex_12 | integer |
gen1_ex_13 | integer |
gen1_ex_14 | integer |
gen1_ex_15 | integer |
gen1_ex_16 | integer |
gen1_ex_17 | integer |
gen1_ex_18 | integer |
gen1_ex_19 | integer |
gen1_ex_20 | integer |
gen2_ex_9 | integer |
gen2_ex_10 | integer |
gen2_ex_11 | integer |
gen2_ex_12 | integer |
gen2_ex_13 | integer |
gen2_ex_14 | integer |
gen2_ex_15 | integer |
gen2_ex_16 | integer |
gen2_ex_17 | integer |
gen2_ex_18 | integer |
gen2_ex_19 | integer |
gen2_ex_20 | integer |
gen3_ex_9 | integer |
gen3_ex_10 | integer |
gen3_ex_11 | integer |
gen3_ex_12 | integer |
gen3_ex_13 | integer |
gen3_ex_14 | integer |
gen3_ex_15 | integer |
gen3_ex_16 | integer |
gen3_ex_17 | integer |
gen3_ex_18 | integer |
gen3_ex_19 | integer |
gen3_ex_20 | integer |
gen4_ex_1 | integer |
gen4_ex_2 | integer |
gen4_ex_3 | integer |
gen4_ex_4 | integer |
gen4_ex_5 | integer |
gen4_ex_6 | integer |
gen4_ex_7 | integer |
gen4_ex_8 | integer |
gen4_ex_9 | integer |
gen4_ex_10 | integer |
gen4_ex_11 | integer |
gen4_ex_12 | integer |
gen4_ex_13 | integer |
gen4_ex_14 | integer |
gen4_ex_15 | integer |
gen4_ex_16 | integer |
gen4_ex_17 | integer |
gen4_ex_18 | integer |
gen4_ex_19 | integer |
gen4_ex_20 | integer |
gen5_ex_1 | integer |
gen5_ex_2 | integer |
gen5_ex_3 | integer |
gen5_ex_4 | integer |
gen5_ex_5 | integer |
gen5_ex_6 | integer |
gen5_ex_7 | integer |
gen5_ex_8 | integer |
gen5_ex_9 | integer |
gen5_ex_10 | integer |
gen5_ex_11 | integer |
gen5_ex_12 | integer |
gen5_ex_13 | integer |
gen5_ex_14 | integer |
gen5_ex_15 | integer |
gen5_ex_16 | integer |
gen5_ex_17 | integer |
gen5_ex_18 | integer |
gen5_ex_19 | integer |
gen5_ex_20 | integer |
ae_kw1 | integer |
ae_kw2 | integer |
ae_kw3 | integer |
ae_kw4 | integer |
ae_kw5 | integer |
filler | integer |
me_tc_rpm1 | integer |
me_tc_rpm2 | integer |
me_tc_rpm3 | integer |
me_tc_rpm4 | integer |
me_tc_rpm5 | integer |
me_tc_ex1 | integer |
me_tc_ex2 | integer |
me_tc_ex3 | integer |
me_tc_ex4 | integer |
me_tc_ex5 | integer |
me_air_cool1 | integer |
me_air_cool2 | integer |
heat_c1 | double precision |
heat_c2 | double precision |
heat_c3 | double precision |
heat_c4 | double precision |
heat_c5 | double precision |
heat_c6 | double precision |
heat_p1 | double precision |
heat_p2 | double precision |
heat_p3 | double precision |
heat_p4 | double precision |
heat_p5 | double precision |
heat_p6 | double precision |
heat_s1 | double precision |
heat_s2 | double precision |
heat_s3 | double precision |
heat_s4 | double precision |
heat_s5 | double precision |
heat_s6 | double precision |
igs_c1 | double precision |
igs_c2 | double precision |
igs_c3 | double precision |
igs_c4 | double precision |
igs_c5 | double precision |
igs_c6 | double precision |
igs_p1 | double precision |
igs_p2 | double precision |
igs_p3 | double precision |
igs_p4 | double precision |
igs_p5 | double precision |
igs_p6 | double precision |
igs_s1 | double precision |
igs_s2 | double precision |
igs_s3 | double precision |
igs_s4 | double precision |
igs_s5 | double precision |
igs_s6 | double precision |
slip | double precision |
foofloat | double precision |
fohandle | double precision |
wind_dir | integer |
intensity | integer |
state_sea | character varying(12) |
soundings | character varying(12) |
ecyl15 | integer |
ecyl16 | integer |
ecyl17 | integer |
ecyl18 | integer |
ecyl19 | integer |
ecyl20 | integer |
rem7 | character varying(12) |
rem8 | character varying(12) |
rem9 | character varying(12) |
rem10 | character varying(12) |
rem11 | character varying(12) |
rem12 | character varying(12) |
rem13 | character varying(12) |
rem14 | character varying(12) |
rem15 | character varying(12) |
mesumplevel | double precision |
oilwat2 | double precision |
tot_steam_time | double precision |
sea_temp | integer |
air_temp | integer |
tg_kw | character varying(4) |
Indexes: noonf_date,
noonf_logno,
noonf_rotation,
noonf_vcode,
noonf_voyageno
The data as i told you are the same db dumped from the production system.
This same dump file was used to populate both (Linux,FBSD) databases.
How is it possible one to have toasted columns whereas the other not??
How can someone identify toasted columns??
Thanx,
Achilleus
Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
> On Wed, 27 Nov 2002, Tom Lane wrote:
>> You didn't say what was *in* the table, exactly ... but I'm betting
>> there are a lot of toasted columns, and that the extra runtime
>> represents the time to fetch (and perhaps decompress) the TOAST entries.
> 278 columns of various types.
> namely,
> [snip]
Hmm, no particularly wide columns there --- but 278 columns is a lot.
I think the extra time might just be the time involved in fetching all
those column values out of the table row?
If you're interested in pursuing it, I'd suggest rebuilding the backend
with profiling enabled so you can see exactly where the time goes.
regards, tom lane
Hello! "Luis Sousa" <llsousa@ualg.pt> wrote in message news:3DE498E4.2050002@ualg.pt... > This is a cryptographically signed message in MIME format. > > --------------ms080209060900030807050408 > Content-Type: text/plain; charset=us-ascii; format=flowed > Content-Transfer-Encoding: 7bit > > Tell me what did you try with limit and group by. > Where's IN, why don't you use EXISTS instead. It runs much master ! > Thanks for the reply! Alright, I'll use EXISTS instead of IN .... I didn't know that EXISTS is faster..... About my query, I have tried : " SELECT * FROM entry where isapproved='y' AND EXISTS (SELECT id FROM subcategory WHERE catid='2') ORDER BY subcatid DESC LIMIT 5; "; This will return only 5 rows.... But when I add the GROUP BY, then I got error " SELECT * FROM entry where isapproved='y' AND EXISTS (SELECT id FROM subcategory WHERE catid='2') ORDER BY subcatid DESC LIMIT 5 GROUP BY subcatid; " : ERROR: parser: parse error at or near "GROUP" Thanks..... Arcadius.